CODEWITHSUNDEEP
sql-serverasp-classicado
Seva Alekseyev
Seva Alekseyev

Reputation: 61408

ADO Connection.ConnectionString does not return back credentials anymore

I have a legacy ASP application that I need to release on a modern server. It uses ADO with SQLOLEDB to talk to MS SQL Server. It's logging on to SQL with username and password.

The app relies on a peculiar behavior of the ADO Connection object that seems to have changed. These days (in ADODB.Connection.6), when you provide a connection string to a Connection and then retrieve it back, you don't get back an exact copy of the original string; specifically, the username and password are stripped from it, and some extra parameters are added.

It looks like older versions of ADO did not strip the credentials in this scenario; the application would never work if it did, and it works. Question - is this documented? Is there a way to enforce the legacy behavior in new ADO?

I really don't want to downgrade ADO on a shared server.

EDIT: just isolated it to a minimal test case. Works on Connection.2.80 on Windows 2003 (msado15.dll v. 2.82.5011.0), fails on Connection.6 on Windows 2008 (dll v.6.1.7600.17036).

EDIT2: officially, the old MDAC 2.8, which ADO is a part of, is not supported on Vista and higher. So downgrade is not really an option.

Upvotes: 2

Views: 1841

Answers (1)

Cheran Shunmugavel
Cheran Shunmugavel

Reputation: 8459

What you're looking for is probably the "Persist Security Info" property. The property is documented here and here (assuming you're using the SQLOLEDB provider; but most providers I've dealt with behave the same in this regard). Basically, when this property is set to False, sensitive information, such as passwords, will be stripped from the connection string when it is read. Conversely, when the property is set to True, the connection string will be returned exactly as it was supplied (including any sensitive information).

I have only found one blog post (but no official documentation) that states that the default value for this property changed from True to False at Windows Vista, which would correspond with Windows Server 2008. This matches your observations.

It should be noted that the new default of False is considered more secure since there is less risk of accidentally revealing sensitive information. The recommended solution here is to re-engineer the application so that it doesn't need to rely on getting the password back from the ConnectionString property.

With that disclaimer out of the way, you can explicitly set the "Persist Security Info" property through the connection string by adding ;Persist Security Info=True. It may also be possible to set the property on the Connection object before opening it (in case you can't change the connection string), but I'm not entirely sure if that would work (you may have to manually set the Provider property first).

' untested examples

' Method 1
connString = "Provider=SQLOLEDB;"
connString = connString & "Data Source=example;"
connString = connString & "Initial Catalog=exampleDB;"
connString = connString & "User Id=user;"
connString = connString & "Password=1234;"
connString = connString & "Persist Security Info=True"

Set conn = Server.CreateObject("ADODB.Connection")
conn.Open connString

' Method 2
connString = "Data Source=example;"
connString = connString & "Initial Catalog=exampleDB;"
connString = connString & "User Id=user;"
connString = connString & "Password=1234;"

Set conn = Server.CreateObject("ADODB.Connection")
conn.Provider = "SQLOLEDB"
conn.Properties("Persist Security Info").Value = True
conn.Open connString

Upvotes: 5

Related Questions