Why is my ConnectionString incorrect..?

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;"


 msgbox (cn.connectionstring)

Set rs = CreateObject("ADODB.Recordset")


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:
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 (20)

  1. Sander says:

    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.

  2. Ken says:

    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.

  3. Mara says:

    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.

  4. SSS says:

    thanks , my problem  resolve using this

  5. Michel Robitaille says:


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


  6. Mark says:

    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

  7. JIYER says:

    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!

  8. Samuel says:

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

  9. Zoe says:

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

  10. Lee says:

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

  11. rolo says:


    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 🙂 )

  12. Pete says:

    Ooh yes, only two killed days with my old Delphi ADO application. Thank you very much.

  13. Graham Waghorn says:

    Extremely grateful. Sanity restored!

  14. Adwin says:

    Fixed our problem! Thanks a lot!

  15. Venkitesh says:

    Thank you, really this blog saved lot of my time

  16. Daniel says:

    Excelente! Muchas gracias

  17. Russ Cole says:

    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!



  18. Fabio Reinert says:

    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….

  19. Geet says:

    Really helpful!! I was also stuck all day.

Skip to main content