3

The following is a connection string generated when I connect to a database using a configuration tool with Microsoft OLE DB Provider for SQL Server.

Provider=SQLOLEDB.1;Password=password;Persist Security Info=True;User ID=sa;
Initial Catalog=database;Data Source=localhost;Use Procedure for Prepare=1;
Auto Translate=True;Packet Size=4096;Workstation ID=computer1;
Use Encryption for Data=False;Tag with column collation when possible=False

If I connect to the same database but with SQL Server Native Client 10.0 I get this connection string.

Provider=SQLNCLI10.1;Integrated Security=\"\";Persist Security Info=False;
User ID=sa;Initial Catalog=database;Data Source=localhost;Use Procedure for Prepare=1;
Auto Translate=True;Packet Size=4096;Workstation ID=computer1;
Initial File Name=\"\";Use Encryption for Data=False;
Tag with column collation when possible=False;
MARS Connection=False;DataTypeCompatibility=0;Trust Server Certificate=False\0

I have a c# application that reads either one of these connection strings and uses it to create a connection to my database like so

SqlConnectionStringBuilder _sqlConnectionStringBuilder = new SqlConnectionStringBuilder();
OleDbConnectionStringBuilder conBuilder = new OleDbConnectionStringBuilder( CONNECTION STRING SHOWN ABOVE);
_initialCatalogValue = (string)conBuilder["Initial Catalog"];
_dataSourceValue = conBuilder.DataSource;

_sqlConnectionStringBuilder.Password = (string)conBuilder["Password"];
_sqlConnectionStringBuilder.UserID = (string)conBuilder["User Id"];
_sqlConnectionStringBuilder.InitialCatalog = _initialCatalogValue;
_sqlConnectionStringBuilder.DataSource = _dataSourceValue;
_sqlConnectionStringBuilder.PersistSecurityInfo = true;

_conn = new SqlConnection(_sqlConnectionStringBuilder.ConnectionString);
_conn.Open();

The problem that when I use the SQL Server native client the password is empty and my SQLConnection will fail on the login. The error I get is "Login failed for user 'sa'".

The OLE DB connection string is successful. Is my login failing for the sql server native client due to some of the classes I am using in c#? Does the Sql Server Native Client 10.0 encrypt the password? Should I try to identify which provider is in the connection string and have two different code paths? If so what would it take to connect?

Basic question is, how can I ensure a successful connection regardless of which connection string I receive (only the two above)?

N.B. I have no control over the connection strings. I can only work with what I am receiving.

Improve this question
2
  • What's the actual error you get? That might help point us in the right direction. Commented Jan 28, 2013 at 14:42
  • I have added the error the the question. Commented Jan 28, 2013 at 14:44

2 Answers 2

Reset to default
1

The second connection string you provide does not include a password; thus conBuilder["Password"] returns an empty string when you set conBuilder.ConnectionString to the second string.

Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

I know that. How then can I connect to the database if that value is missing? Is there any way to find where the password may have been stored when the connection string was created?
Assuming you know the database password for the sa account (which I presume you do, given that you had it in the OleDbClient-derived connection string), you would add a "Password=password;" section to your connection string. (Full documentation for the SqlConnection.ConnectionString property is here.)
If, on the other hand, you do not know what the sa account password is, you'll have to look at whatever your source for the native client connection string is to see why it's not providing the password. A possible issue is that your sample native client connection string includes "Persist Security Info=false;" while the ConnectionString documentation notes that "The .NET Framework Data Provider for SQL Server does not persist or return the password in a connection string unless you set Persist Security Info to true"
I am unable to change the sql string so cannot set persist security to true. I am able to change the Integrated Security setting to SSPI in the c# code. When I do that the connection works just fine but I am not sure what the ramifications are of doing that. How would it affect other aspects of the connection if any?
As noted in the MSDN documentation for SqlConnection.ConnectionString, when Integrated Security is set to sspi, "the current Windows account credentials are used for authentication." Thus, your application would have whatever database permissions the currently logged in Windows user has (which, obviously, might not be the same as the sa Sql Server account).
0

The problem was solved as follows.

There was a main application that was making use of the above connection strings and was doing the following.

The application would take the connection string. If the connection string's provider is SQL Server Native Client (SQLNCLI10.1) the application checks for persistent security. If it cannot find any it adds IntegratedSecurity=SSPI to the connection string and then connects using windows authentication instead. Whether or not this is the right (or secure thing to do) that is what was being done.

To 'answer' the question. You can take in the connection string and if you do not find a password you can set IntegratedSecurity=SSPI. This will allow you to connect using windows authentication instead of SQL Server authentication. I am not advising that you do, but it will work.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.