How to define a connection string to a SQL Server 2008 database?

Question

I'm using MS Visual Studio 2010 to create an application with SQL Server 2008 database access, but what I did to create the database was add a new "SQL Server 2008 Database Project", it added it, and shows me everything on my Solution Explorer, but how do I write the connection string to connect to it, because I wrote this one, and it didn't work.

SqlConnection cnTrupp = new SqlConnection("Initial Catalog = Database;Data Source = localhost;Persist Security Info=True;");

update:

I used this one:

cnTrupp = new SqlConnection("database=DB_Trupp;server=.\\SQLExpress;Persist Security Info=True;integrated security=SSPI");

But when I use the cnTrupp.Open() it tells me that the login failed.

Answer 1

You need to specify how you'll authenticate with the database. If you want to use integrated security (this means using Windows authentication using your local or domain Windows account), add this to the connection string:

Integrated Security = True;

If you want to use SQL Server authentication (meaning you specify a login and password rather than using a Windows account), add this:

User ID = "username"; Password = "password";

Answer 2

Standard Security
Data Source=serverName\instanceName;Initial Catalog=myDataBase;User Id=myUsername;Password=myPassword;

Trusted Connection
Data Source=serverName\instanceName;Initial Catalog=myDataBase;Integrated Security=SSPI;

Here's a good reference on connection strings that I keep handy: ConnectionStrings.com

Answer 3

Copy/Paste what is below into your code:

SqlConnection cnTrupp = new SqlConnection("Initial Catalog = Database;Data Source = localhost;Persist Security Info=True;Integrated Security = True;");

Keep in mind that this solution uses your windows account to log in.

As John and Adam have said, this has to do with how you are logging in (or not logging in). Look at the link John provided to get a better explanation.

Answer 4

Check out the connection strings web site which has tons of example for your connection strings.

Basically, you need three things:

• name of the server you want to connect to (use "." or (local) or localhost for the local machine)
• name of the database you want to connect to
• some way of defining the security - either integrated Windows security, or define a user name / password combo

For example, if you want to connect to your local machine and the AdventureWorks database using integrated security, use:

server=(local);database=AdventureWorks;integrated security=SSPI;

Or if you have SQL Server Express on your machine in the default installation, and you want to connect to the AdventureWorksLT2008 database, use this:

server=.\SQLExpress;database=AdventureWorksLT2008;integrated Security=SSPI;

Answer 5

Instead of writing it in your code directly I suggest you make use of the dedicated <connectionStrings> element in the .config file and retrieve it from there.

Also make use of the using statement so that after usage your connection automatically gets closed and disposed of.

A great reference for finding connection strings: connectionstrings.com/sql-server-2008.