Home

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

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

Comments

  • 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.
  • A year has come and gone. Any input regarding this?
  • 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?
  • 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.
  • It may seem like over-engineering, but is there any merit to allow unencrypted to those connections which connect to non-"production" servers?
  • Can I ask the reason for needing SQL Authentication rather than Windows Authentication? It it because you're using a cloud-based SQL Server?
  • And would a certificate-based solution be an option?
  • 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.
  • 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..
  • 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.
  • nescafe: I'll update LINQPad so that your workaround will work if you specify SQL Authentication with a username and blank password.
  • Thanks.

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