Excel and system dsn

I have a system dsn setup to an external SQL database using SQL authentication.
I want to be able to use that authentication when setting up the connection in Excel to grab the external data.

Every query is asking me for a username and password.

How can I have Excel use the authentication that I have setup with the system DSN?
Ken AbareOwnerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

SteveCost AccountantCommented:
Set the connection up as you have...
But on the Definition tab of the Connection Properties dialog tick the Save Password box.
This should save the information you require for logging
Save Password
Ken AbareOwnerAuthor Commented:
The username and password is still stored in the connection string.  I don't want this information in the connection details at all.

Is there a way to use the authentication in the system DSN instead of storing the username and password in the connection string?

This spreadsheet will be shared all over the office and I don't want the user who is generating the queries to have their credential accessible to everyone in the office.
SteveCost AccountantCommented:
You can use windows authentication, but would then need to set up the users access on the server side.
Then just set the DSN to use Windows Authentication.
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Ken AbareOwnerAuthor Commented:
Windows Authentication is not an option.
The SQL database is hosted on the Vendor's servers.  
The users in the office need to access the data via SQL Authentication in the DSN.
SteveCost AccountantCommented:
A System DSN is stored in the registry and does not have the ability to store a password.
If you want to store the password, you'd have to use a File DSN or Save it in the file.
There may also be a VBA option which could allow for 'hiding' the connection, as the VBA module can be password protected.
But I don't think you can acheive what you really want, which is that the PC DSN has the login details stored.
Ken AbareOwnerAuthor Commented:
I wonder what the point of having the username and password in the DSN if you need the same information in the connect string.
SteveCost AccountantCommented:
I tend to agree with you, the DSN should allow for saving the password in the DSN.
Then Excel could just use it as required without any fuss.
However it does not allow this and you are left with either a visible password in the connection string (not great security there) or the user has to type the password every time.
This is a tad frustrating, but those seem to be the options available.
Yes, the password can be hidden by using VBA. Here's how:

' Required settings for VBA Editor - Tools > References > Microsoft ActiveX Data Objects X.X Library

    Dim cn              As ADODB.Connection
    Dim rs    			As ADODB.Recordset
    Dim strSQL          As String

    strSQL = "select myField " & _
             "  from mySchema.myTable " & _
             " where myField2 = 'Hello' "

    Set cn = CreateObject("ADODB.Connection")

   ' This is a DSN-less connection 
   ' See ConnectionStrings.com for more examples

    If cn.State <> adStateOpen Then
        MsgBox "Could not connect to the database. Please connect to the VPN, then try again."
        Exit Sub
    End If

    Set rs = cn.Execute(strSQL)

    If Not rs.EOF Then
        ActiveWorkbook.Sheets("Sheet1").Range("A1").CopyFromRecordset rs
    End If

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Ken AbareOwnerAuthor Commented:
Your password is still part of the VBA script and not really hidden.  
Also, if we ever change the password then the code will need to be changed as well as the system DSN.
The system password gets hidden by locking the VBA code with its own password. In the VBA editor, select the Tools menu > VBAProject Properties > Protection tab > check/enable the Lock option, and enter the password twice.

The system DSN is no longer needed/used when the VBA code uses the DSN-less method shown in my sample.

The password-changing issue would also have happened with your original request, were it technically possible. The system DSN would still have had to be changed. That is why some databases have "ODBC" accounts with passwords that never expire. If the system allows it, those accounts are locked out from actually logging in - they only connect remotely via ODBC.
Ken AbareOwnerAuthor Commented:
I will give it a try.  We have several connects with different queries to the data source so one location for the password will be awesome!
Ken AbareOwnerAuthor Commented:
hiding the code is a good work around.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.