Freigeben über

Why is my ConnectionString incorrect..?

  • Artikel

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

Comments

  • Anonymous
    July 13, 2010
    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.

  • Anonymous
    June 30, 2011
    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.

  • Anonymous
    July 12, 2012
    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.

  • Anonymous
    March 04, 2013
    thanks , my problem  resolve using this

  • Anonymous
    May 10, 2013
    THANK YOU VERY MUCH!!! From french guy who didn't need to suffer too long time.... MERCI BEAUCOUP!!!!

  • Anonymous
    May 21, 2013
    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

  • Anonymous
    June 05, 2013
    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!

  • Anonymous
    January 17, 2014
    The comment has been removed

  • Anonymous
    April 29, 2014
    Thank you some much!!! you really save my day!

  • Anonymous
    July 02, 2014
    Thank you...this was exactly what I was looking for.

  • Anonymous
    November 16, 2014
    Hi, have a similar problem : after close and re-open, just the source property is reset to the value that it had during design time (old VB6 Programm using Data environment) The given hint with "Persist Security Info" does not help :-( any suggestions ? (other than rewriting the program :-) )

  • Anonymous
    January 09, 2015
    Ooh yes, only two killed days with my old Delphi ADO application. Thank you very much.

  • Anonymous
    February 04, 2015
    Extremely grateful. Sanity restored!

  • Anonymous
    April 22, 2015
    Fixed our problem! Thanks a lot!

  • Anonymous
    August 17, 2015
    Thank you, really this blog saved lot of my time

  • Anonymous
    September 02, 2015
    Excelente! Muchas gracias

  • Anonymous
    October 22, 2015
    Many thanks - this was the root cause for our very large legacy application not running under IIS7 on Windows Server 2008 having been fine under 2003 for over 10 years. After many days building different environments, playing with security settings, trawling through internet posts and adding debug code all over the place to identify the area of contention in the end it was just this simple fix. Most grateful! cheers Russ

  • Anonymous
    October 31, 2015
    Thank you.

  • Anonymous
    October 04, 2016
    Oh My Gosh!!! Thank you so much!!! I'm from Brazil and I'm working with VB6 and VB.net and I apreciate that. A million of thanks for you....

  • Anonymous
    February 10, 2017
    Really helpful!! I was also stuck all day.