5

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

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

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

But trying a version of that makes the .net code throw a wobbler as he's written it using SQLServer drivers so its not liking the Provider stuff.

Here is the orginal connection string from his code:

Server=(local);Initial Catalog=TheDatabase;User Id=TheUser;Password=ThePassword;

I've been to http://www.connectionstrings.com/sql-server-2005 and tried several of the options from there, these all get "SQL Server does not exist or access denied" (what a lovely mixed error message that is!):

  • Data Source=localhost;Integrated Security=True;Initial Catalog=TheDatabase
  • Data Source=localhost\SQLEXPRESS;Integrated Security=True;Initial Catalog=TheDatabase
  • Data Source=MyLaptop\SQLEXPRESS;Integrated Security=True;Initial Catalog=TheDatabase
  • Server=MyLaptop\SQLEXPRESS;Initial Catalog=TheDatabase;User Id=TheUser;Password=ThePassword;

I've created logins for MyLaptop/IUSR_MyLaptop, MyLaptop/ASPNET, MyLaptop/IWAM_MyLaptop in SQL Express and given them all read/write permissions to my DB and set their default DB to be TheDatabase.

What the heck am I doing wrong and how can I debug the problem some more?

UPDATE: Special Thanks to Chris for all his pointers, got there in the end, if you are having similar problem please read all the comments there are lots of links and tips on how to track them down.

Improve this question
4
  • May be a daft question but what is the actual instance name of the sql express server you're trying to connect to? Is it localhost\sqlexpress or something else? Cut the app out of the equation and just try to connect to sql via sqlcmd by doing 'sqlcmd -S servername\instancename -E' in a command prompt and see if it connects. Commented Jul 23, 2009 at 10:31
  • The instance is actually called MSSMLBIZ but left it as SQLExpress for clarity. Tried teh above and got this: SQL Network Interfaces: Error Locating Server/Instance Specified [xFFFFFFFF]. Sqlcmd: Error: Microsoft SQL Native Client : An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections.. Sqlcmd: Error: Microsoft SQL Native Client : Login timeout expired. Checked in Server Config and all it switched on? Commented Jul 23, 2009 at 10:43
  • Going to try a reinstall to try to reset everything and start again... Commented Jul 23, 2009 at 11:23
  • Reinstall was a pig! Thanks to this article for the help incase anyone else gets the "The SQL Server failed to start" error on Win XP SP2: support.microsoft.com/default.aspx?scid=kb%3Ben-us%3B920114 Commented Jul 23, 2009 at 14:10

4 Answers 4

Reset to default
1

Can you advise exactly what is in the config?

Are you using the block - in which 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 are you getting the connection string from app settings - in which case I guess your provider is set in code inside the app itself?

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

2 Comments

app settings, its .Net Version 1 (due to a limitation of where it will be hosted). Reinstall is not going well either...one of those days...
And thanks for your help too Chris, much appreciated, DB setup is not really my bag...
0

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

I presume the instance is running and does allow connections over tcpip?

Improve this answer

9 Comments

Reading that article now, downloading the PortQry too. Yes instance is running and from what I've read and seen it "should" be allowing tcpip. Disabled dynamic ports and set it to always use 1434. Do I need that though if running local?
No - it shouldn't matter if connecting locally but you've got something strange happening - how are you administering the server at the moment. SSMS? Are you connecting with a windows or sql login?
In Configuration Manager - check the Client Config to see what protocols are enabled on for connecting and the order they're applied.
Yes using Microsoft SQL Server Management Studio Express with a Windows login. Goes in just fine. Also I've switched on Dynamic ports again by setting it to 0 and changed the tcpip port to 1433 which I believe is the right one (1434 is for the SQL Browser from what I've read). More info in a minute...
Was that with the -E switch (windows auth)? You can now try sqlcmd -S localhost\mssmlbiz -U username -P password to verify that you can log in using the credentials that you think should be able to use.
|
0

Shouldn't your datasource read: Data Source=localhost\sqlexpress too?

Improve this answer

1 Comment

Tried that one, added it in above.
0

You don't mention granting rights for 'TheUser' to access the database on the server - if you're restored from another server you may had a sid mismatch.

Try running

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 remap things.

Improve this answer

4 Comments

I don't appear to have that stored procedure available? Msg 2812, Level 16, State 62, Line 1 Could not find stored procedure 'sp_update_users_login'.
sorry - it should have been sp_change_users_login ... but it's probably not relevant if you can't even get a basic connection to the server.
Ok got that to run, there only one entry, one from when the developer ran the DB I guess. Not one of mine. I did restore the DB but added my users back in afterwards. Whats this report telling me Chris?
That will tell you mismatched users - i.e. where the sid of the user on your server doesn't match the one in the database itself. A common issue when moving dbs between servers. If the content in the report is not the username you're trying to connect with then just ignore it.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.