5

I have deployed a website to IIS and it is trying to access the database via the connection string.

My SQL Server and connection string is correct as I have tested it in the following ways:

  1. My development environment streams data correctly
  2. Connection to SQL Server is good as I could see from SQL Server Management Studio
  3. Other pages of the website that are deployed are correct and only those pages that stream data is failing.

The problem is that it is only through the IIS website we have this error and when I run it from Visual Studio it is all right.

Here is the stack code I get:

Stack trace error

My connection string:

<add name="cnnSQLDB" 
     connectionString="server=**********;database=*********;Integrated Security=True;User ID=******;Password=******;" 
     providerName="System.Data.SqlClient"/>
alroc
  • 27,574
  • 6
  • 51
  • 97
user2140740
  • 101
  • 2
  • 8
  • Sounds like a firewall issue to me. Can you ping the sql server from your web server? Is the port open for sql server? – Sean Lange Jun 09 '17 at 14:36
  • Is the SQL Server port open? Port 1433 needs to be open for SQL traffic to come through. – Jack Marchetti Jun 09 '17 at 15:06
  • 3
    I think your connectionstring is wrong, using both integrated security and supplying username and password. Check out https://www.connectionstrings.com/sql-server/ – David Libido Jun 09 '17 at 16:11
  • @SeanLange Yes I can ping the sql server from my web server. The way I tested this is I used the following command 'telnet servername 1433' and I got a blank message. Does it mean that the port 1433 is available and open? – user2140740 Jun 09 '17 at 19:47
  • @DavidLibido Its nothing to do with the connection string as I checked this link before and it wouldnt work in development environment at all. – user2140740 Jun 09 '17 at 19:52

2 Answers2

0

Make sure that port 1433 is open on your firewall. That is the port SQL Server uses.

Jack Marchetti
  • 15,536
  • 14
  • 81
  • 117
  • The way I tested this is I used the following command 'telnet 1433' and I got the following message...Could not open connection to the host, on port 23: Connect failed. – user2140740 Jun 09 '17 at 19:49
  • the syntax to use is: telnet sqlservername 1433 – seagulledge Jun 09 '17 at 22:47
  • 1
    @seagulledge I'm getting a blank screen when i tried with 'telnet sqlservername 1433' and that means the port is open for sql connections. Can it be that the firewall is blocking requests? – user2140740 Jun 10 '17 at 05:38
0

I was able to solve this issue based on hints provided here. I had to do the following changes:

  1. Update the SQL connection string and set "Integrated security=false". In that way it will use the login credentials provided in the SQL connection string.
  2. Create a new user as provided in the SQL connection string and GRANT the necessary accesses.
  3. When I copied the databases from the production environment to a new environment the GRANT permissions for the user mentioned in the connection string was not updated.

But by checking the port status via the command 'sp_readerrorlog' gave me clues and led me to a solution. Some other links that helped me were: this and this

user2140740
  • 101
  • 2
  • 8