1

I already asked this question before, but without any good response, my problem is that I have a local database (aspnetdb.mdf) in my app_data folder, here is the connection string in the web.config : 

<add name="ApplicationServices" 
     connectionString="data source=.\SQLEXPRESS;Integrated Security=SSPI;AttachDBFilename=|DataDirectory|\aspnetdb.mdf;User Instance=true" 
     providerName="System.Data.SqlClient" />

so my site works fine in my local iis server, but when i put the site in my host server, I get this error message : A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified).

In the other server I have sql server 2008 (the remote connection is accepted and i have the same instance name SQLEXPRESS). I really tried everything that I can but without any good results. I will really appreciate your help, thx.

wben
  • 247
  • 2
  • 7
  • 20

1 Answers1

6

This format indicates a local connection:

.\SQLEXPRESS

From another server, you'll need to specify a server name or IP address, e.g.:

192.168.1.205\SQLEXPRESS
YOURMACHINE\SQLEXPRESS

You'll also need to validate that the instance is SQL Server Express. From your other nearly identical question it looks like a default instance.

You should also stop using this silly user instance / attachDbFilename technique. Attach your database to the instance properly, then reference the logical database name using Initial Catalog=aspnetdb; in your connection string. This user instance feature was deprecated for multiple reasons, and this is certainly one of them.

Of course there may be other network issues that will prevent this, e.g. if the server is in a different domain, there are firewall settings interfering, etc. But your other server is never going to be able to access your machine using a dot for the server name.

EDIT providing baby steps because obviously my advice is not being followed. Assuming you have actually attached the aspnetdb database on the new server, try this please:

 connectionString="data source=192.168.85.124\SQLEXPRESS;
   Integrated Security=SSPI;
   Initial Catalog=aspnetdb;"

If you aren't using SQL Express named instance after all, try:

 connectionString="data source=192.168.85.124;
   Integrated Security=SSPI;
   Initial Catalog=aspnetdb;"

For SQL authentication use the same username/password you use in Management Studio:

 connectionString="data source=192.168.85.124;
   User ID=username; Password=password;
   Initial Catalog=aspnetdb;"

This is troubleshooting 101 stuff. If you still can't get this to work, you may have to hire a consultant for 10 minutes to fix it for you since it's impossible to figure out what you've done and why you can't get it to work.

Community
  • 1
  • 1
Aaron Bertrand
  • 272,866
  • 37
  • 466
  • 490
  • Hi @Aaron Bertrand, thx for replying, Here is my new connectio string : . I get the same error ! – wben Aug 05 '12 at 21:45
  • Can you give me please an exemple ? – wben Aug 05 '12 at 21:52
  • 1
    Did you attach the aspnetdb database, or do you expect to be able to continue using this `|DataDirectory|` nomenclature and referencing a physical file instead of a logical database? – Aaron Bertrand Aug 06 '12 at 00:18
  • but when I try to specify the Ip adress I get this error : Invalid value for key 'attachdbfilename'. Can you give please an exemple of a correct connection string ^ – wben Aug 07 '12 at 13:57
  • Why are you still using `AttachDbFileName`? Didn't I say multiple times to stop using that? – Aaron Bertrand Aug 07 '12 at 13:58
  • You're right @Aaron Bertrand, I'm not someone who is very good in asp, Like you see I'm enable to fix this problem since sunday, I need some exemple, how the correct connection string should look ? thx for your help – wben Aug 07 '12 at 14:12
  • Yes, but I still have the same error. but, I was looking in to services in my host server(services.msc) and I can't see the service Sql server(SQLEXPRESS) I see Sql server(MSSQLSERVER), I think that the problem come from here ? – wben Aug 07 '12 at 14:26
  • I do not know why I'm relieved, but I get another error message : Cannot open database "aspnetdb" requested by the login. The login failed. Login failed for user 'NT AUTHORITY\SYSTEM'. – wben Aug 07 '12 at 14:40
  • 2
    Well it sounds like you chose SSPI for security but didn't mean to do that either. Did you create a login on the new SQL Server that has access to the database? Might I suggest Access as a much easier database to use and manage? – Aaron Bertrand Aug 07 '12 at 14:43
  • In my SSMS The username and password are required, but I don't see the relation with my aspnetdb.mdf because this database is located in my App_Data folder (local database), actually in my project I have two database the first one is located in Sql server 2008 and it's work great and the second one is my local database aspnetdb.mdf. (there is no username and password required to access in this one) – wben Aug 07 '12 at 15:03
  • mmm, I tried to chande my pool Identity to Network service I get the same error but this time with :The login failed. Login failed for user 'NT AUTHORITY\NETWORK SERVICE'. before my pool Identity was in local system – wben Aug 07 '12 at 15:09
  • I added the username and the password in my connection string, but I still have an error , another one actually : Cannot open database "aspnetdb" requested by the login. The login failed. Login failed for user 'w2012'. (I know that you did everything that you can, and I wanna say a lot of thanks to you)) – wben Aug 07 '12 at 15:26
  • So what username/password did you use when connecting via SSMS? – Aaron Bertrand Aug 07 '12 at 15:29
  • username = w2012 ; password = w2012 – wben Aug 07 '12 at 15:31
  • I don't see why using this to connect to my aspnetdb.mdf ? because in my local server I didn't use this and it's work ! – wben Aug 07 '12 at 15:33
  • Connecting to a file that you own using your Windows credentials is very different from connecting to a database hosted inside SQL Server with SQL Server credentials. I strongly recommend you read up on SQL Server and authentication. – Aaron Bertrand Aug 07 '12 at 15:34
  • Yes, but I used the sql authentication not windows authentication – wben Aug 07 '12 at 15:36
  • The connection string in your question is not using SQL authentication. – Aaron Bertrand Aug 07 '12 at 15:40
  • Hi @Aaron Bertrand, I fixed the problem , but not like I wanted, Actualy I moved the database aspnetdb.mdf to my SSMS and It`s work, but the consequence of this, is if you want to add a role or a create a new user the db aspnetdb.mdf in App_data will be updated instead of the one in SSMS – wben Aug 07 '12 at 16:31
  • 2
    I think you need to delete the MDF file in App_data and stop referencing it from your app, from Visual Studio, from SSMS, etc. You only need *one* copy of your database, and the right one to manage is the one that's actually attached to your instance of SQL Server, not some static file in your user directory that gets copied every time you create a new reference to it. – Aaron Bertrand Aug 07 '12 at 17:35