8

Information: We have a 3rd party application we use for production at our company. This program uses a DSN to connect up to our SQL Server 2012 database through ODBC. This application works properly under Server 2003 (MADC 2.8) however when i bring it to Server 2008 x86 (DAC 6.0) i am getting a connection failed with "Microsoft OLE DB Provider SQL Server Login failed for user XXX". I have a feeling this is due to the default of "persist security info" changing from True to False on Windows servers starting with server 2008 and greater (Change made in DAC 6.0). I do not have access to changing the connection string inside the application as it is 3rd party. as seen in this article

Question: Is there any way to change ADO.Net's behavior so that this value is defaulted to True rather than False outside of the connection string. I would like to be able to at least prove or disprove that this is the feature causing the issue.

Note: I realize this is a huge security issue tampering with this setting and we will be taking the correct precautions if it is changed to ensure the server and application are isolated.

Solution: Provided by @William below. If you are updating your SQL Server 3rd party application From Server 2003 to Server 2008+ and you are getting a connection like above where on 2003 you did not, set the password for the SQL account to blank (temporarily or in staging only, this is very dangerous to leave blank in production) to test if the application works again when a blank password is provided. If it does then the application is not setting the Persist Security Info in the connection string and the value that was defaulting to true now defaults to false. Your application may be limited to use under server 2003 and may not function properly on server 2008+. There is no way that i could find to get the value to default back to true.

Random Developer
  • 1,334
  • 2
  • 12
  • 31
  • 1
    Have you tried already to edit your DSN Connection in the Registry under [HKLM]\SOFTWARE\ODBC\ODBC.INI\ ? – KarmaEDV Jul 10 '15 at 13:37
  • I have. So near as i can tell this is what happens every time the application starts up. If the application has not been set up it creates a new .DSN file in the root then allows the end user to configure this file with settings, when the user logs in the settings in the ODBC registry are updated from the last successful log in, then the reg DSN is used for the app. The connection string is built in the object code and is not stored in the registry. i have tried adding additional keys to the registry under the DSN but they get overwritten or do not get loaded into the object in the code. – Random Developer Jul 10 '15 at 14:58
  • 1
    I have a similar problem. We have a legacy application (which we have no source for) that we need to port over to use Windows 2008, and the app relies on being able to pull the connection string back out of the connection object to create more connections. It was written before the effects of Persist Security Info were well-known, so it does not specify it all (implicitly relying on the default). With the default value changing to False in Windows Vista and later, the application will not run there. I have not found any way to set the system (or user) default for Persist Security Info. – William Jul 10 '15 at 20:12

1 Answers1

4

If the application is using the existing connection object to construct new connection strings, then there could be a workaround for this issue - depending on the application and on security constraints.

Since this issue probably does not exist when using integrated security, we can assume "SQL Server standard security" is a requirement. If a blank password is used for the SQL server account, then, when the new connection string is constructed, it will then be correct (somewhat by accident).

William
  • 690
  • 5
  • 13
  • Enjoy the bounty, this is a solid way to prove if persist security info was not set in the connection string. Like you said leaving a password like this as blank is somewhat dangerous and should not be done in a production setting. In resolving our problem we will need to think very carefully if we plan to implement this change. Thank you for your assistance! – Random Developer Jul 10 '15 at 21:26