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
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 thisAnonymous
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 oneAnonymous
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 removedAnonymous
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 timeAnonymous
September 02, 2015
Excelente! Muchas graciasAnonymous
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 RussAnonymous
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.