Geek Answers Handbook

SQL Express connection string.

SQL Express 2005 runs locally. I have a project written by another person working on the same machine. All I want to do is connect to it, can't it be that hard?

This is the one I use in my old classic ASP code to get to another database running in the same instance:

Provider = SQLOLEDB; Data Source = MYLAPTOP \ MSSMLBIZ; Persist Security Info = True; User ID = TestUser; Password = letmein; Initial Catalog = TestDB

But having tried the version that makes the .net code, he throws out the wobbler when he recorded it using the SQLServer drivers, so he doesn’t like the provider materials.

Here is the source connection string from its code:

Server = (local); Start Directory = TheDatabase; User ID = TheUser; Password = ThePassword;

I was at http://www.connectionstrings.com/sql-server-2005 and tried several options from there, they all get "SQL Server does not exist or access is denied" (what a great mixed error message!):

  • Data Source = localhost; Integrated Security = True; Start Directory = TheDatabase
  • Data Source = localhost \ SQLEXPRESS; Integrated Security = Truth; Start Directory = TheDatabase
  • Data Source = MyLaptop \ SQLEXPRESS; Integrated Security = True; Start Directory = TheDatabase
  • Server = MyLaptop \ SQLEXPRESS; Start Directory = TheDatabase; User ID = TheUser; Password = ThePassword;

I created logins for MyLaptop / IUSR_MyLaptop, MyLaptop / ASPNET, MyLaptop / IWAM_MyLaptop in SQL Express and gave them all read and write permissions to my database and set their default database in TheDatabase.

What the hell am I doing wrong and how can I debug the problem a bit more?

UPDATE: Special thanks to Chris for all his pointers, got there in the end, if you have the same problem, please read all the comments, there are many links and tips on how to track them.

+4
source share
4 answers

With this error message in your comment, you should run through the elements at http://blogs.msdn.com/sql_protocols/archive/2007/05/13/sql-network-interfaces-error-26-error-locating-server-instance -specified.aspx

I assume the instance is running and allows connections through tcpip?

+1
source

Can you specify exactly what is in the config?

Do you use a block - in this case a valid connection string would be:

<add name="connection" providerName="System.Data.SqlClient" connectionString="Data Source=localhost\MSSMLBIZ;Initial Catalog=TheDatabase;Integrated Security=True" />

or

 <add name="connection" providerName="System.Data.SqlClient" connectionString="Data Source=localhost\MSSMLBIZ;Initial Catalog=TheDatabase;Integrated Security=False;User Id=TheUser;Password=ThePassword;Application Name=AppName;" />

Or do you get a connection string from the application settings - in this case, I suppose your provider is installed in the code inside the application itself?

+1
source

Should your data source read: Data source = localhost \ sqlexpress too?

0
source

You did not mention granting rights to "TheUser" to access the database on the server - if you are restored from another server, you may have a sid mismatch.

Try to run

sp_update_users_login 'report'

against the db in question.

If it returns the user account in the report, try:

sp_update_users_login 'update_one', 'theuser', 'theuser'

to reassign things.

0
source

More articles:


All Articles