SQL Server 2005 SQL Authentication Connection String

Question

Hello, I'm building an application that connects to SQL Server 2005. It currently uses Windows authentication, but I'd like to switch to SQL Authentication (I believe it is also sometimes called Mixed Authentication). My current connection string is:

"Data Source=LOCALHOST;Initial Catalog={0};Integrated Security=SSPI"

That's for Windows authentication, but for SQL, I am thinking:

"Data Source=LOCALHOST;Initial Catalog={0};user id={1};password={2}"

Is this the correct way? The code assumes that:

• {0} is the name of the database
• {1} is the username
• {2} is the password

I'm switching to SQL authentication because I'm thinking of connecting to a SQL Server instance on a remote server - is SQL authentication the right way to do this, and would I just have to enter the IP where "LOCALHOST" is currently?

Thanks!

UPDATE: Thank you for all the great answers, guys! All of them were wonderful and very helpful, I can't even decide which one to award "accepted answer" to, but I have voted up all of them because they rock. Thanks again!

Answer 1

Yes this will work exactly as you said.

"Data Source=11.22.33.44;Initial Catalog={0};user id={1};password={2}"

Answer 2

You can also use uid instead of "User Id" and pwd instead of "password":

"Data Source=LOCALHOST;Initial Catalog={0};uid={1};pwd={2}"

In place of LOCALHOST, you would either use the IP of the remote machine, or the DNS Name. Note that if multiple instances of SQL Server exist on the remote machine, you need to specify the instance under Data Source - e.g. "Data Source=11.22.33.44\SQLEXPRESS".

Answer 3

You go in the right way, but I think that looking at Connection Strings may be much more helpfull to you than any answer in here.

Answer 4

There is an app for that: SqlConnectionStringBuilder:

SqlConnectionStringBuilder scsb = new SqlConnectionStringBuilder();
scsb.DataSource = "LOCALHOST";
scsb.InitialCatalog = ...;
scsb.IntegratedSecurity = false;
scsb.UserID = ...;
scsb.Password = ...;

SqlConnectionStringBuilder scsb = new SqlConnectionStringBuilder();
scsb.DataSource = "LOCALHOST";
scsb.InitialCatalog = ...;
scsb.IntegratedSecurity = true;

You can then extract the connection string from the builder's ConnectionString property. This way is error proof and you can later modify other properties like ConnectTimeout or AsynchronousProcessing, and you won't have to remember the string syntax.