Connection String for SQL Server 2008 (version 10.0.1600) in Application Configuration File

Question

I am new to create connection string and application configuration file. I use an example which showed connection to SQL Server CE using file which is what I do not want instead I want to connect to SQL Server 2008 Standard edition.

While exploring about connection string on several links like http://www.connectionstrings.com/sql-server/ i found that the Connection string uses the Property "Data Source = " and in some places it uses "Server=" which is quiet confusing.

Here is what I have in my application configuration file.

<connectionStrings>
    <add name="ShareManagement" 
         connectionString="Data Source=localhost" 
         providerName="System.Data.SqlClient"/>
</connectionStrings>

I want someone to tell me which properties need be used and what should be their respective values. (I am using default sa user as UserID and a password and using SQL Server authentication mode. My SQL Server database is installed on same machine/server on which my Visual Studio solution / application reside).

Ragards.

Answer 1

You can use either server= or Data Source= (those two are equivalent), and you can use either database= or Initial Catalog= (again: those are equivalent) - take your pick, use whatever you prefer.

But you just need to define at least

1. server,
2. database,
3. either Integrated Security=SSPI (for integrated, Windows authentication) or User id=abc;Password=xxxx for SQL Server authentication

You need at least these three pieces of information.

So if you want to use integrated security (Windows authentication), use this connection string:

<connectionStrings>
    <add name="ShareManagement" 
         connectionString="server=(local);database=AdventureWorks;Integrated Security=SSPI;"
         providerName="System.Data.SqlClient"/>
</connectionStrings>

but if you want to use SQL Server authorization for a user John with a password secret, use this connection string:

<connectionStrings>
    <add name="ShareManagement" 
         connectionString="server=(local);database=AdventureWorks;User ID=John;Password=secret;"
         providerName="System.Data.SqlClient"/>
</connectionStrings>

Since I'm almost exclusively using these connection strings to connect to a standard relational database server, I personally prefer to use server=.... and database=...... - those just seem more natural, clearer and more intuitive to me. But again: you can also use those other key strings - they're 100% equivalent!