Data Access Technologies

(Data Access, XML, SSIS, LINQ, System.Data ...)

Why is my ConnectionString incorrect..?

Why is my ConnectionString incorrect..?

  • Comments 10

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

Leave a Comment
  • Please add 2 and 4 and type the answer here:
  • Post
  • Even though cn.ConnectionString is truncated, I noticed that you can still see the extended properties by looking at the "Extended Properties" item in the properties collection of the connection object.

  • This is helpful.  The Persist Security Info keyword is the root cause of a problem I have.

    I have an Access app that has no real tables, but links that tie to some views in a backend server.  The app prompts the user for name and password, etc., and then creates linked tables.  I do not use DSN, but build connection string for each table based on the user input.  It has been working fine.

    But for new users with newer machines/drivers, the script failed with the message: ODBC--connection to '...' failed.  After the script has successfully created and opened a connection, it then modifies and adapts the connection string to create linked tables.  It failed because the connection string returned is no longer fully describing the opened connection, but merely: Provider=MSDASQL.1.  Sure enough, the app failed to connect a table with this connection string.

    By adding Persist Security Info=True to the original connection string, the connection string of the opened connection now has all the necessary information.  The app can then modify the string and set up connections for the linked tables.

    Thanks for the information.

  • Thank you so much!  This worked for me, even though the connection string did not appear to be truncated.  We run very old software on Windows 7 and could not get a particular program to run.

  • thanks , my problem  resolve using this

  • THANK YOU VERY MUCH!!!

    From french guy who didn't need to suffer too long time....

    MERCI BEAUCOUP!!!!

  • Thank you for posting this. I had a similar problem when transferring applications onto Windows Server 2008 (from 2003). My connection woes were solved by your solution. Nice one

  • Thank you SO much for posting this solution. I had been struggling for hours to figure out why my application that had worked for years would not work when ported to a Windows7 machine. Adding "Persist Security Info=true" proved to be the solution!

  • Oh my God, i spent a week trying to figure out this issue. Your post has helped. Thank you so much.

  • Thank you some much!!! you really save my day!

  • Thank you...this was exactly what I was looking for.

Page 1 of 1 (10 items)