1

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.

atricapilla
  • 615
  • 6
  • 12
  • 15

5 Answers5

4

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.)

Mac
  • 141
  • 1
  • 7
  • 1
    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? – Travis Heeter Dec 24 '14 at 15:51
  • 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. – Mac Dec 29 '14 at 16:09
  • 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). – Travis Heeter Jan 06 '15 at 12:15
  • 1
    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. – Mac Jan 06 '15 at 13:01
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.

Maximus Minimus
  • 8,987
  • 2
  • 23
  • 36
  • 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`? – IMTheNachoMan Nov 10 '16 at 03:18
  • @imthenachoman : You should ask this as a separate question. – Maximus Minimus Nov 10 '16 at 06:15
1

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.

tufelkinder
  • 125
  • 7
0

I don't believe there is the ability to do this. Using a trusted NT connection to SQL Server implies that there is no password sent while authenticating to the server and that the existing NT token is used to authenticate. In other words, SQL Server "trusts" the NT authentication. It will use whatever user is logged in at the time of connection.

Brian Knight
  • 1,175
  • 2
  • 8
  • 17
0

I have found that using the Windows Credential Manager works quite well. You can add the windows credential directly. The trick is you need to have the fully qualified domain name including the port, as well as your complete Active Directory username with domain qualifier, so something like mydb.myinternaldomain.com:1433 and the myinternaldomain\myusername with your password. Then you can add mydb.myinternaldomain.com as an ODBC source and windows will magically swap in the right credentials. This also works for Sql Server Management Studio.

It doesn't appear to work for non-windows native apps, that you still need runas.

benjaminRRR
  • 101
  • 1