Ask the Expert

Give me an ODBC connect, with SQL authentication

I must open an ODBC connection to a DB and must use a connection that looks like this: "DSN=myDSNname". The user must be able to setup SQL Authentication on the DSN and use it to connect. I am getting 28000 error saying that login failed because I do not have a trusted connection. Can you tell me what I am doing wrong? It works if I set up the DSN to use NT authentication, but it will not accept the SQL authentication. ?R.S.

When you get these results, it is usually due to the way SQL Server is installed and configured to work with authentication. By default, it will allow only integrated Windows authentication. In order to enable SQL authentication, you must open the SQL Server Enterprise Manager, right-click on the SQL server machine node, and setup mixed mode authentication in the Security section.

Alternatively, you can modify the registry to change the setting easily by modifying the value of the LoginMode entry under MSSQLServer key. These will be located in different places depending on your installation, but usually it will be in HKLM\SOFTWARE\Microsoft\MSSQLServer\MSSQLServer\LoginMode.

The value should be a REG_DWORD with the value 2. If you save the following script to a file with a .vbs extension, it will be just a matter of double-clicking it on the target machine:

' Changes the login mode of MSDE/SQL Server
Option Explicit
Dim shell
Set shell = CreateObject("WScript.Shell")
Rem Write For default SDK tutorials installation.
shell.RegWrite "HKLM\SOFTWARE\Microsoft\Microsoft SQL Server\NetSDK\MSSQLServer\LoginMode", &H2, "REG_DWORD"
Rem Write For standalone MSDE installation (from asp.net site) or full SQL Server setups.
shell.RegWrite "HKLM\SOFTWARE\Microsoft\MSSQLServer\MSSQLServer\LoginMode", &H2, "REG_DWORD"

This was first published in March 2005

Back to top