SQL Server Express 2008 connection issue; trying to connect to localhost

0
votes

I've become stuck with an issue that I am sure is on- the- nose simple to fix, but this is my first time trying to connect to SQL Server Express 2008 R2. It's on my local machine, and my error is the standard:

"Cannot open database "Market" requested by the login. The login failed. Login failed for user 'KR\user'."

In SSMS, here are some of the connection properties: Authentication Method: Windows Authentication User Name: KR\user Server Name: KR\SQLEXPRESS Instance Name: SQLEXPRESS

Here is my connection string: Data Source=localhost\SqlExpress;Initial Catalog=Market;Integrated Security=True

Here is how I'm attempting to connect via C#:

connectionString = "Data Source=localhost\\SqlExpress;Initial Catalog=Market;Integrated Security=True";    
sqlConnection = new SqlConnection();
sqlConnection.ConnectionString = connectionString;
sqlConnection.Open();

Edit:My attempt with a different syntax was this connection string: 

"Data Source=.\SQLExpress;AttachDbFilename='C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS\MSSQL\DATA\Market.mdf';Integrated Security=True;

Using it with the same calling code changes the error to:

An attempt to attach an auto-named database for file C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS\MSSQL\DATA\Market.mdf failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share.

Edit: I found the properly formatted connection string by right- clicking the database in database explorer, going to properties, then copy/ pasting the connection string property into my code. Thx...

2
c#sql-server-2008connection-string
where you have localhost for SQLExpress could you change it to either \\localHost\SqlExpress or .\SQLExpress\ I use SQLServer 2008 R2 Enterprise version so constrings are a bit different.. could you possibly change Intergrated Security to User ID=YourUserId;Trusted_Connection=yes here is a great link for connection strings as well dofactory.com/Connect/Connect.aspx – MethodMan
How are you connecting? In code or through the VS IDE? If in code, then please show the code. – John Saunders
@John: I added the calling code per your request. I do also have the database listed in the VS IDE, do I need to be aware of the connection status there, due to effects of it on my calling code? – StatsViaCsh
@DJKRAZE I cringe to ask this, but is my user ID "KR" based on the info above? I'm sure my error is of this nature.. – StatsViaCsh
@DJKRAZE: I'm adding a string to my post above that is very close to your suggestion, with a new error message for it.. – StatsViaCsh

2 Answers

1
votes

You need to setup the permissions for this user on your instance of SQL Server Express.

A server login (using Windows Authentication), a database user that is attached to the login and the permissions on the databases this user will be accessing all need to be setup.

1
votes

Looks like you NT security. Have checked that the "KR\user" has been granted rights on the Marketing database. Open the database "Market" > Security > Add User. Grant them DBO rights and try again.