Why is my ConnectionString incorrect..?

Folks,
I wanted to post this experience as I struggled for a couple of days trying to figure out what’s wrong in my connection string when I handled a migration scenario from Windows 2000 to Windows 2008. I had a bunch of VBScripts doing database update jobs running successfully over years on the Windows 2000/XP machines using ODBC System DSNs. 

After porting those to the new Windows 2008 Server the code started to fail with:

Error: [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified
Code: 80004005
Source: Microsoft OLE DB Provider for ODBC Drivers I double-checked the System DSNs, made sure that they exist and Test Connection is successful.

The VBScript code is exactly the same and below is the snippet where I open the connection and a Recordset where it is erroring out:
==================================================================

Dim rs

Dim cn

Set Cn = CreateObject("ADODB.Connection")

cn.ConnectionString = "DSN=Test;UID=XXX;PWD=XXX;"

 cn.Open

 msgbox (cn.connectionstring)

Set rs = CreateObject("ADODB.Recordset")

 rs.CursorLocation=3

rs.Open "select * from sys.dm_exec_connections", cn.ConnectionString

MsgBox rs.RecordCount

 ==================================================================

 I pulled off my hair for a couple of days trying to figure this out till I found that when I printed out the ConnectionString property (highlighted in the above code) it produces different values in the working and the non-working environment. In the old Windows 2000 box (working), it is:
Provider=MSDASQL.1;Persist Security Info=True;Extended Properties="DSN=Test;UID=XXX;PWD=XXX;APP=Microsoft (R) Windows Script Host;WSID=XXX;" However, in the Windows 2008 box (non-working), the ConnectionString seemed to be truncated and it only returned:
Provider=MSDASQL.1; With a strong belief that this is the root cause of the issue I continued my research till I found out that the behavior change was introduced by a security fix. Its goal is to hide sensitive information when your connection string doesn’t explicitly contain *Persist Security Info=true*. You will see the same behavior since vista, because the fix was already rolled out to VISTA RTM and later. Now, if Persist Security Info is not set to True at the time the data source is initialized, the data source cannot persist sensitive authentication information. Furthermore, a call to a property that contains sensitive authentication information, such as a password, returns a default value instead of the actual password.

Since vista, connection string will always return only “Provider=MSDASQL.1” if you don’t explicitly add *Persist Security Info=true*. It because ADO will always regard the value of *Extended Properties* sensitive and hide it by default, in the meantime, MSDASQL return all of its properties with *Extended Properties*. Consequently, you only see “Provider=MSDASQL.1”. The below is a full connection string. Provider=MSDASQL.1; Persist Security Info=True; Extended Properties=”Driver=SQL Server; Serve=XXX;UID=XX;PWD=XXX;APP=Microsoft Windows Script Host; WSID=XXX;DATABASE-master” Workaround: You have to explicitly add * Persist Security Info=true*, so that ADO understand that you explicitly need sensitive information and return to you. The following is updated connect string

cn.ConnectionString = " DSN=Test;UID=XXX;PWD=XXX; Persist Security Info=true ” I appended Persist Security Info attribute in my connection String, set it to True and the scripts started working fine in the Windows 2008 environment. When I print the ConnectionString value out, it now does show the entire connection string with the Extended Properties much as I expect.

 Hope this helps, Cheers !

Author : Debarchan(MSFT), SQL Developer Engineer, Microsoft

Reviewed by : Ambuj(MSFT), SQL Developer Technical Lead , Microsoft