2

SQL server 2008 db on one physical server was migrated to other physical server with SQL express 2008. All tables and data were successfully migrated, but I am not able to connect with my asp.net mvc app to the new express db.

In my connection string, I have just changed the IP add. of the physical server:

<add connectionString="server=XX.XX.XX.XXX;database=hgw;user id=hgw;password=hgw4p1!;Trusted_Connection=false" name="HGWDBContext" providerName="System.Data.SqlClient" />

what generates following error:

Server Error in '/HGW' Application. The system cannot find the file specified. 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: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)]

I have seen somewhere that the migration of db does not migrate users and passwords. But even when I change user id and pass to actual admin's it generates the same error. (btw in Server Properties - Connections - remote connection is allowed)

It is just something I am missing in the connection string or do I have to create new user id or it is something else? thx

nevo
  • 47
  • 2
  • 7
  • Usually SQL Server Express is installed with a named instance. `server=XX.XX.XX.XXX\SQLEXPRESS` – Steve Jun 16 '14 at 07:52
  • I did that already, but mostly the same error, different is just the err number at the end "(provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)" – nevo Jun 16 '14 at 07:59
  • Did you try to connect using Sql Server Management Studio? Is the server on your local network? If yes try to search for it using SSMS. (Not sure if it counts or not, but check with or without the browser service started) – Steve Jun 16 '14 at 08:02
  • No prob connecting with SSMS (yes it is on my local network). I have the app opened with IIS Manager and db with SSMS, I see the db and its tables, users, roles etc. (btw I did not get the 'browser service started', can you pls explain?) – nevo Jun 16 '14 at 08:08
  • And at the login prompt of SSMS the server name is specified just with the IP address or has also the instance name? – Steve Jun 16 '14 at 08:10
  • Ok, something new. I have changed the server IP to the server name as you hinted. And got new error "Login failed for user 'hgw'. The user is not associated with a trusted SQL Server connection." (btw in this case server name + \SQLEXPRESS generates the same above 'error: 26') – nevo Jun 16 '14 at 08:19
  • Probably the server has been installed with only Windows Authentication and not in a mixed mode authentication. In this case only users authenticated by windows can access the database system and hgw is not a windows user. If you try to connect using that user with SSMS? – Steve Jun 16 '14 at 08:38
  • Not able to connect to SSMS with hgw user. So I changed user id and pass to my windows login, but the same error still - "Login failed for user '(win auth. user)'. The user is not associated with a trusted SQL Server connection." – nevo Jun 16 '14 at 08:57
  • Of course now you need to change the connection string and leave only "Trusted_Connection=True" See a [review here](http://stackoverflow.com/questions/1229691/difference-between-integrated-security-true-and-integrated-security-sspi) – Steve Jun 16 '14 at 09:03
  • With Trusted_Connection=true or Integrated Security=true I have now this error "Cannot open database "hgw" requested by the login. The login failed. Login failed for user 'IIS APPPOOL\ASP.NET v4.0'." – nevo Jun 16 '14 at 09:38
  • Thanks Steve, you have showed me the right direction. After I searched for the above errors I have found this answer from Ateev Gupta [link](http://stackoverflow.com/questions/3583605/login-failed-for-user-sa-the-user-is-not-associated-with-a-trusted-sql-server) and the tweak in SSMS SQL Server Surface Area Config together with creation of new user made it. Pls post an answer focused on the change from server IP to server name and I will mark it as answer. thx again – nevo Jun 16 '14 at 12:25

1 Answers1

0

SQL Server Express is installed using a named instance. So your connection string should be changed to

server=YourServerName

You could try it using the search feature of Sql Server Management Studio and use the same string used by the application to connect to Sql Server Express

nevo
  • 47
  • 2
  • 7
Steve
  • 213,761
  • 22
  • 232
  • 286
  • So my connection string now looks like this: ``. As I mentioned, user logins were not migrated with the database so I had to create new login in SSMS - Databases - Security - Logins right click and New Login... – nevo Jun 16 '14 at 12:35