What is the best way to share Queries with SQL Authentification on an Network Drive

SWO

The Linq file include the User and the encrypted Password. It looks that he password is encrypted with DPAPI. That mean that i can call this Query with lprun.exe but no other user in my company, they can't connect to the SQL Database . What is the best strategy to share queries with other user without additional configuration(create named connections on each user computer) and external (Instand share) Cloud.
Thanks Wolfgang

jnus

Got similar problem here - using lprun in a teamcity build to generate some statistics from our logs on various build servers. Need to have everything in source control, however login fails.

jnus

A year has come and gone. Any input regarding this?

JoeAlbahari

The following will work, but it requires one-time manual intervention on each machine:

• Right-click the connection that automatically appears and click Properties
• Enter your credentials
• Tick 'Remember this connection'
• Click OK

Any other queries that use the same connection ID will then pick up the credentials.

Otherwise, I don't know of a solution. LINQPad could allow credentials to be saved unencrypted, but would you be happy with this?

jnus

Hmm - Just about to start another discussion regarding this, and saw your reply. Setting this up manually is not really an option. Build servers are created and deleted on demand. Being able to specify credentials in clear text would actually be acceptable - since we use readonly user to connect to the mssql server and only developers have access to the source and build servers. It would be great to have the option to override the default behavior and save the credentials unencrypted. Any change to see this in the near future?

Thanks for responding!
J.

Tormod

It may seem like over-engineering, but is there any merit to allow unencrypted to those connections which connect to non-"production" servers?

JoeAlbahari

Can I ask the reason for needing SQL Authentication rather than Windows Authentication? It it because you're using a cloud-based SQL Server?

JoeAlbahari

And would a certificate-based solution be an option?

jnus

Regarding windows authentication, we do not want to rely on AD's, since this creates more complexity. E.g. we don't have a central AD in our test environments (different systems creates one if needed) but use a seperat AD for PreProd and Prod. It quickly gets to be a pain to manage + not very flexible when having infrastructure as code. Long story short, sql authentication just makes managing/configuring more simple, when scripting stuff.

A certificate-based encryption would be fine - build-/triggerservers could have this preinstalled and seems fairly simple. Although a bit more expensive to implement, than a simple clear text option.

jnus

Fyi, I've created a suggestion on UserVoice ->http://linqpad.uservoice.com/forums/18302-linqpad-feature-suggestions/suggestions/9654657-allow-remote-execution-of-scripts-with-lprun

nescafe

This seems to work:

Redefine the connection string in the "Additional connection string parameters":
Integrated security=False;User ID=sa;Password=secret

You can set the authentication mode to Windows.

The password is saved in plain text:

<Query Kind="Expression">
  <Connection>
    <ID>62fbfb5e-714b-488b-9e48-7aec98e12dd2</ID>
    <Server>myserver.local</Server>
    <NoPluralization>true</NoPluralization>
    <NoCapitalization>true</NoCapitalization>
    <IsProduction>true</IsProduction>
    <Database>MyDB</Database>
    <ShowServer>true</ShowServer>
    <DriverData>
      <ExtraCxOptions>Integrated security=False;User ID=sa;Password=secret</ExtraCxOptions>
    </DriverData>
  </Connection>
</Query>

users.Take(100)

If it doesn't work on first try, redefine the connection first (Create Similar Connection) and try again..

Chowder

Maybe the script should be able to log into the database by itself. This would allow you to enter it in plain text, prompt for it, or use whatever you wanted really.

JoeAlbahari

nescafe: I'll update LINQPad so that your workaround will work if you specify SQL Authentication with a username and blank password.

nescafe

Thanks.

I also noticed that the "Addition connection string parameters" textbox doesn't allow the text to be selected using CTRL-A hotkey.