SQL Server 2008 password ending in a semicolon

Question

Let's say I have a password that looks like this: password;

How can I get it to work with a semicolon as the last character. The password works in SSMS and ODBC, but not with the connection string. I've tried " around it but that does not seem to work.

<add name="DbConn" connectionString="Data Source=LOCALHOST;Database=MYDB;Trusted_Connection=no;User Id=myuser;Password=password;" providerName="System.Data.SqlClient" />

This is for an ASP.NET web application. As far as I can tell, it is impossible. UPDATE: It IS possible!

Answer 1

Encapsulate your password in single quotes. e.g. given the password iloveachallenge; your connection string should contain Password='iloveachallenge;';.

I am using the following code to connect to SQL Server 2008 R2.

  var sqlConnection = new SqlConnection()
            {
                ConnectionString = "Server=DT2719MOD;Database=abs2;User Id=TestUserLogon;Password='iloveachallenge;';"

            };
        sqlConnection.Open();
        Console.WriteLine(sqlConnection.State);
        sqlConnection.Close();

Edit: Also tried to use the connection string that you have and it works on my machine.

ConnectionString="Data Source=DT2719MOD;Database=abs2;Trusted_Connection=no;User Id=TestUserLogon;Password='iloveachallenge;';" 

Answer 2

To include values that contain a semicolon, single-quote character, or double-quote character, the value must be enclosed in double quotes.

From: http://msdn.microsoft.com/en-us/library/windows/desktop/ms722656(v=vs.85).aspx

Basically you have there all the escaping procedures of a connection string.

Answer 3

Yes, this is possible. The answer is given in Pete's answer in this thread: Escape ;(semicolon) in odbc connection string in app.config file

Basically, you need to put single quotes around the Username/Password fields, not the characters you want to escape, which is where I was going wrong.

To steal Pete's example:

initial catalog=myDB;UserId=MyUser;Password=abc;123;multipleactiveresultsets=True;

needs to become:

initial catalog=myDB;UserId='MyUser';Password='abc;123';multipleactiveresultsets=True;