odbc connection string dosen't work on windows 7

Question

I use ODBC connection for my application (controlpanel -> administrativetools -> odbc -> DSN) to sql server 2008, and windows authentication it works perfect on windows xp,

but now I run it on windows7 and sql server 2008 with sql server authentication. when I make the DSN and query the connection its ok and I got this message (TESTS COMPLETED SUCCESSFULLY!)

but when I run the application I got this error

(ERROR [28000] [Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user ''. ERROR [28000] [Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user ''.)

Its some thing wrong whit DSN but I dont know what it is ,

maybe Make DSN is diffrent in windows 7????

thanks alot

I think a little more and I found that Maybe my password doesn't save for my user, when I make that DSN?

How can I save it???

Answer 1

Isn't it 64 bit Win7 and 32 bit application?

If so you must configure ODBC DSN in 32 bit Windows subsystem. There are 2 version of odbcad32.exe in 64 bit versions of Windows. In c:\windows\system32 you will find 64 bit that configure ODBC for 64 bit applications and in c:\windows\syswow64 there is 32 bit version you should use to configure ODBC for 32 bit programs.

Answer 2

I found out

I make my DSN as befor . but when I whant to use it in my application (connection to sql server with sql authentication ) I should Pass the (userid and password to it like bellow)

Dim con As New Odbc.OdbcConnection("dsn=dsnName;UID=sa;PWD=123")

Answer 3

Normally making ODBC connection by SQLConnect() needs connection string, username and password. Username and password in administration panel are used only for connection test and each application should give username and password. In some environments username and password is in connect string. For example I use Python odbc module where connection is made with one parameter: http://docs.activestate.com/activepython/2.6/pywin32/odbc__odbc_meth.html

conn = odbc.odbc('dsn_alias/user/password')

You can find more about connection string to SQL Server 2008 here: http://connectionstrings.com/sql-server-2008

Answer 4

I just ported a vb6 app that used classic ado and a DSN to get to the sql data to Win Server 2008 R2 and Sql Server 2008 R2. Two links to check out when you have this issue are posted below. Basically you run a UDL test and that hooks onto your oledb drivers that are installed to the OS and gives you an idea if you really can get to the db across the driver. The beautiful part about it is that the "UDL" file you create in these links will contain for you the exact connection string needed to update your application. You can copy that connectionstring in place of your old one and forego setting up a new DSN on the machine.

x64 UDL Test

x86 UDL Test

From what I can see the oledb drivers now require provider and datacompatibilitylevel variables set in the connection string in addition to other properties.

Really makes you appreciate sqlClient class. Ha.