0

I have moved a project that used to work fine but now it cannot acces my database anymore. I use Entity Framework and have connected to the database in SQL Server 2012 successfully. Here is what my connection string looks like :

<connectionStrings>
    <add name="PrincipalServerContext" 
         connectionString="Data Source=ServerName\SQLEXPRESS;Initial Catalog=PrincipalServerDB;Integrated Security=True" 
         providerName="System.Data.SqlClient"/>
</connectionStrings>

When I launch the website, after authentication I retrieve data, but it throws the exception

System.Data.SqlClient.SqlException

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)

at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)

I believe it might have to do with several things but am not sure, and don't know how to fix them :

  1. User permissions in SQL Server
  2. Connection strings could be wrong but I checked and re checked a hundred times

Could not make it work like this, and the exception is throw on Linq queries to access DB.

EDIT I did try to publish the web site to test, and have a second connection string :

<connectionStrings>
    <add name="PrincipalServerContext_DatabasePublish" 
         connectionString="Data Source=ServerName\SQLEXPRESS;Initial Catalog=PrincipalServerDB;Integrated Security=True" 
         providerName="System.Data.SqlClient"/>
</connectionStrings>

Though I'm just trying to debug on the new computer I set up the project, if I remove this line, I have an exception. So maybe my program is searching for this DB and finds nothing?

System.InvalidOperationException cannot find PrincipalServerContext_DatabasePublish connection string

(I translate from french)

Flexabust Bergson
  • 732
  • 14
  • 34
  • 1
    Where have you moved it from? You're using integrated security so make sure the Windows account you're running the application from has access to your database. – benjrb Sep 29 '17 at 15:36
  • 1
    Does your user account have access to that database? Problem is most likely because of `Integrated Security` – Grizzly Sep 29 '17 at 15:38
  • 1
    `PrinciparlServerContext_DatabasePublish` is not the same as `PrincipalServerContext_DatabasePublish`. you have a type-o – Igor Sep 29 '17 at 15:40
  • ok, any pointers? I tried in SQL Server Management Studio to go in properties and select user permissions, but there were only two choices, `public` and `guest` – Flexabust Bergson Sep 29 '17 at 15:40
  • @Igor Mb typo error I have to translate from french – Flexabust Bergson Sep 29 '17 at 15:41
  • @Igor But I provide only `PrincipalServerContext` and no where is there the `Database_Publish`. – Flexabust Bergson Sep 29 '17 at 15:44
  • So you can connect to the SQL Server with no problem? If that's the case, when you connect to the server, via SSMS.. click the `+` sign next to Security.. then right-click on `Logins` and add a new login.. make sure to make it a sql server authentication.. not windows.. then set your default database at the bottom.. then go to User Mapping on the right.. and select the role membership you want for that account.. then delete Integrated Security from your connection string and use the account credentials you just created. – Grizzly Sep 29 '17 at 15:44
  • I managed to make it work by changing the databasepublish connection string. Don't know why it works – Flexabust Bergson Sep 29 '17 at 15:55

1 Answers1

0

It likely isn't related to user permission. Rather, the new server you moved the project to cannot access the server SQL Server is located on.

If the new server has telnet, try this in Command Prompt on the Application Server: 

telnet serverName 1433

it'll likely hang there trying to connect. Few things you could do:

  • Make sure there is no firewall blockage between the two servers if they are on different zones.

  • Check if SQL Server's Server has an inbound rule open for port 1433 (SQL Server Express doesn't open this port by default, but Enterprise and Standard do).

  • Check to see SQL Server is accepting TCP IP and Named Pipe connections through Sql Server Configuration Manager.

farzaaaan
  • 410
  • 3
  • 9