Home / server / Questions / 116701
Accepted
atricapilla
Asked: 2010-02-26 04:14:25 +0800 CST

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

  • 772

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.

sql-server windows-xp odbc dsn

5 Answers

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

    • 4
  2. Best Answer

    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.

    • 1

  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.

    • 1

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

    • 0

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

    • 0