DSN, ODBC: How to create DSN using different windows account

Question

Is there a possibility to create DSN (ODBC to SQL Server) using different Windows account than current coputer login account? I’m trying to create System DSN to SQL Server and I would like to create this connection using Windows authentication using my admin account. I’m creating this DSN using my normal windows account.

Solution 1

A System DSN by definition applies to the entire computer irrespective of which logon account is used, so (if I'm reading your question right) that means that (1) the answer is "yes", and (2) you will need Admin rights to create the DSN.

Have you looked as DSN-less connections, by the way? I believe that they would be much more appropriate for your requirement, and would also remove the need for client configuration before your app could be used.

Solution 2

You can also use the "runas" command to launch the ODBC Data Source Administrator under your admin account while logged under your normal account. That would enable you to configure and test the connection without receiving a "user is not associated with a trusted SQL Connection" error.

Here is an example Command Prompt command:

runas /netonly /user:domain\adminusername "C:\Windows\System32\odbcad32.exe"

As Jimmy said, the DSN definition would not be tied to the admin account, but would use whatever Windows Authentication account you were logged in as when using the connection later. (So you would need to use "runas" again to launch any programs that used the connection, unless you were logged in under your admin account.)

Solution 3

I'm going to add to Mac's answer that yes, this definitely works to set up the ODBC connection and it does work for a while. Probably for as long as the Kerberos authentication stays active. Unfortunately, this isn't the permanent authentication I would prefer for a System DSN. Here is the batch file that I use to launch odbcad32.exe:

net use \\dbserver-host /user:DOMAIN\username
runas /netonly /user:DOMAIN\username C:\Windows\syswow64\odbcad32.exe

Note that this can result in you being prompted to login twice, but it seems to work more consistently than runas by itself.

Comments

[Travis Heeter]

I tried this. No luck though. I created the DSN, but an error came back saying that the computer I created it on didn't have access to the data source. Any ideas?

[Mac]

Did "Test Data Source" fail during your ODBC configuration? If it failed with "the user is not associated with a trusted SQL Server connection" then that could relate to the credentials or domain specified in your runas command. If you're getting a different error, you may have a server of firewall connectivity issue and you may want to open a separate question.

[Travis Heeter]

I ran further tests and it turns out that even though I created the DSN as one user, it still used the computers credentials. So lets say I have a PC called FOO and a user I want to use called BAR: I created the DSN as you instructed: I ran cmd as admin, then ran odbcad32.exe as BAR and created a DSN. When I ran my program that uses the DSN, it said Login failed for user FOO (the name of the computer that is running the program).

[Mac]

Right. As mentioned above, using runas with the ODBC administrator is useful to test the connection and confirm it's configured properly, but you would still need to use the same runas command to launch any program that needed to run under that admin account.

[IMTheNachoMan]

How can I create a DSN-less connection so I can use with MS Access to connect to an SQL server using company\user2 when I am logged into my machine (and using MS Access) as company\user1?

[Maximus Minimus]

@imthenachoman : You should ask this as a separate question.