SQL Server 2005 SQL Authentication Connection String

Question

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!

eKek0 · Accepted Answer · 2009-08-10 18:14:39Z

6

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.

answered Aug 10, 2009 at 18:14

eKek0

23.4k26 gold badges94 silver badges121 bronze badges

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

1 Comment

Maxim Zaslavsky Over a year ago

Right, that was the website i was looking for! I remembered that there was a great site that has all this info, but couldn't remember what it was called - seeming a very deceptive name!

bv8z · Accepted Answer · 2009-08-10 18:14:12Z

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".

answered Aug 10, 2009 at 18:14

bv8z

9753 gold badges9 silver badges19 bronze badges

Comments

Remus Rusanu · Accepted Answer · 2009-08-10 18:34:00Z

2

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.

answered Aug 10, 2009 at 18:34

Remus Rusanu

296k42 gold badges459 silver badges583 bronze badges

2 Comments

sfuqua Over a year ago

This is great, but it does make the process of extracting configuration values into your config file more difficult.

Remus Rusanu Over a year ago

@sfuqua: not realy, since the SCSB construnctor acceps a connection string. When building the SCSB froma config setting conn string, you also validate the format in the process.

gbn · Accepted Answer · 2009-08-10 18:11:16Z

1

Yes this will work exactly as you said.

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

answered Aug 10, 2009 at 18:11

gbn

434k84 gold badges602 silver badges690 bronze badges

Comments

sfuqua · Accepted Answer · 2009-08-10 18:47:40Z

1

If you do not have a common Active Directory domain between the local and remote server, then I think you will need the SQL authentication. However, if you do have a common ADS domain, then I recommend using it – otherwise you have to either use a common SQL account for everyone (and then use an appropriate mechanism to encrypt the password) or create separate SQL accounts for each user, thereby duplicating data.

Be very careful with that Initial Catalog setting. If that value can be supplied by user input, then it might be used to try to attack another database, if you don't have good validations in place to protect against it. Sorry if I'm preaching to the choir :-).

answered Aug 10, 2009 at 18:47

sfuqua

5,8731 gold badge36 silver badges33 bronze badges

Collectives™ on Stack Overflow

SQL Server 2005 SQL Authentication Connection String

5 Answers 5

1 Comment

Comments

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

1 Comment

Comments

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related