locked
Database connection failed by using IP address RRS feed

  • Question

  • User1327654160 posted

    Hi, 

    Please help.  It is urgent.

    My web application is using ASP .NET C#. 

    My application works fine when I configure the database connection by server name.  However, in the external production site, we have to use the IP address to make the connection.  We have already open the firewall port. 

    <connectionStrings>
      <clear />
      <add name="XXXConnectionString" connectionString="Data Source=IP_ADDRESS;Initial Catalog=databasename;User Id=USERNAME; password=PASSWORD;"
       providerName="System.Data.SqlClient" />
    </connectionStrings>

    However I got this error when I run the application on the external site.

     Error Message:Login failed for user 'abruser'.
    Stack Trace:   at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
       at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
       at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
       at System.Data.SqlClient.SqlInternalConnectionTds.CompleteLogin(Boolean enlistOK)
       at System.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo serverInfo, String newPassword, Boolean ignoreSniOpenTimeout, Int64 timerExpire, SqlConnection owningObject)
       at System.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(String host, String newPassword, Boolean redirectedUserInstance, SqlConnection owningObject, SqlConnectionString connectionOptions, Int64 timerStart)
       at System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance)
       at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance)
       at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection)
       at System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnection owningConnection, DbConnectionPool pool, DbConnectionOptions options)
       at System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject)
       at System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject)
       at System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject)
       at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)
       at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
       at System.Data.SqlClient.SqlConnection.Open()
       at System.Data.Common.DbDataAdapter.QuietOpen(IDbConnection connection, ConnectionState& originalState)
       at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
       at System.Data.Common.DbDataAdapter.Fill(DataTable[] dataTables, Int32 startRecord, Int32 maxRecords, IDbCommand command, CommandBehavior behavior)
       at System.Data.Common.DbDataAdapter.Fill(DataTable dataTable)
       at SubscribersTableAdapters.ReportByProductPerReleaseTableAdapter.GetSubscribersByUserRoleAndType(String userRole, String rptType)
       at SubscriptionBLL.GetSubscriptionsByUserRoleAndType(String userRoleID, String rptType)
       at Reports_StaticReports.Page_Load(Object sender, EventArgs e)
       at System.Web.Util.CalliHelper.EventArgFunctionCaller(IntPtr fp, Object o, Object t, EventArgs e)
       at System.Web.Util.CalliEventHandlerDelegateProxy.Callback(Object sender, EventArgs e)
       at System.Web.UI.Control.OnLoad(EventArgs e)
       at System.Web.UI.Control.LoadRecursive()
       at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint

     

    When I checked the datasource which using the connection string from the config file, I got this error:

    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)
    Stack Trace:   at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
       at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
       at System.Data.SqlClient.TdsParser.Connect(ServerInfo serverInfo, SqlInternalConnectionTds connHandler, Boolean ignoreSniOpenTimeout, Int64 timerExpire, Boolean encrypt, Boolean trustServerCert, Boolean integratedSecurity, SqlConnection owningObject)
       at System.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo serverInfo, String newPassword, Boolean ignoreSniOpenTimeout, Int64 timerExpire, SqlConnection owningObject)
       at System.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(String host, String newPassword, Boolean redirectedUserInstance, SqlConnection owningObject, SqlConnectionString connectionOptions, Int64 timerStart)
       at System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance)
       at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance)
       at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection)
       at System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnection owningConnection, DbConnectionPool pool, DbConnectionOptions options)
       at System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject)
       at System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject)
       at System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject)
       at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)
       at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
       at System.Data.SqlClient.SqlConnection.Open()
       at System.Data.Common.DbDataAdapter.QuietOpen(IDbConnection connection, ConnectionState& originalState)
       at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
       at System.Data.Common.DbDataAdapter.Fill(DataTable[] dataTables, Int32 startRecord, Int32 maxRecords, IDbCommand command, CommandBehavior behavior)
       at System.Data.Common.DbDataAdapter.Fill(DataTable dataTable)
       at SubscribersTableAdapters.ReportByProductPerReleaseTableAdapter.GetSubscribersByUserRoleAndType(String userRole, String rptType)
       at SubscriptionBLL.GetSubscriptionsByUserRoleAndType(String userRoleID, String rptType)
       at Reports_StaticReports.Page_Load(Object sender, EventArgs e)
       at System.Web.Util.CalliHelper.EventArgFunctionCaller(IntPtr fp, Object o, Object t, EventArgs e)
       at System.Web.Util.CalliEventHandlerDelegateProxy.Callback(Object sender, EventArgs e)
       at System.Web.UI.Control.OnLoad(EventArgs e)
       at System.Web.UI.Control.LoadRecursive()
       at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)

    Please advice how I can configure the connection string to use IP address.

    Thanks a lot.

    Wednesday, November 18, 2009 12:28 PM

Answers

  • User1327654160 posted

    Hi,

    I have another connection string connect the ASP role membership provider.

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

    Do I need to change it to the remote connection by using IP address?  Will this causes my problem?

    Thanks,
    Monica

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, November 20, 2009 12:19 PM

All replies

  • User2130758966 posted

    There are several things you need to do setup a server remotely... would be quite a long reply to explain them all:

    • Create a login username password and setup the permissions on it
    • Configure the server to allow SQL Server and Windows Authentication mode for connectiosn
    • Connect to the server including the instance name like 192.168.1.1\SQLEXPRESS
    • Connect to the server including the port name like 192.168.1.1\SQLEXPRESS,1456
    • Configure the server to listen for connections via TCP/IP

    Anywhere you would like me to start? lol

    Wednesday, November 18, 2009 1:12 PM
  • User1327654160 posted

    We are using a server user account to make the connection to the database.  The database is SQL server 2005.

    Please share the last 3 topics.  Thanks!

    Wednesday, November 18, 2009 1:30 PM
  • User2130758966 posted

    Basically you need to load up Sql Server Configuration Manager (on the database server). There is a shortcut in the sql server program group in the start menu.

    Look in the SQL ServerServices node to see whats running on the computer, on mine i see SQL Server (SQLEXPRESS). the SQLEXPRESS bit is the instance name so if I wanted to connect to it I would use:

    ipaddress\SQLEXPRESS


    Unless you have explicitly set a port you shouldn't need to specify it.


    In the SQL Server Network Configuration node you should open up the "Protocols for INSTANCENAME" and check that TCP/IP is enabled. Then look in it to find out what ports its listening on. Dynamic ports are no good for remote access, you should set it to a port 1433 which is the default port.



    To test out if this is all working you can then do the following:

    1. Click Start | Run
    2. Type CMD
    3. Press enter
    4. Then you can use SqlCmd command line utility to test out the connection by typing something like:

    sqlcmd -S ipaddress\instanceName -U username -P password

    and see if it connects (the S U and P should be caps).

    type sqlcmd /? for other options

    also try replacing ipaddress with localhost to see if its just your firewall blocking access....

    Wednesday, November 18, 2009 3:51 PM
  • User1327654160 posted

     Hi,

    Thanks for your advice, however, I still get the same problems.  I was not be able to test the connection on the remote machine since it does not have sqlcmd file.

    The steps have taken:
    Open TCP port 1433 between external web server and SQL server.
    Follow this instruction: Configure a server to Listen on a Specific TCP Port (SQL Server Configuration Manager)
     http://msdn.microsoft.com/en-us/library/ms177440.aspx

    Please confirm whether I need to open "SQL Server Browser" if I already have TCP port opened?  I read this article:
    http://support.microsoft.com/kb/914277

    What can try next?

    Thanks!

    Thursday, November 19, 2009 12:16 PM
  • User2130758966 posted

    Please confirm whether I need to open "SQL Server Browser" if I already have TCP port opened?  I read this article:
    http://support.microsoft.com/kb/914277

    No you dont require it.


    have you tried adding the port on to your connection string?

    What is your instance name? Presuming its SQLEXPRESS your connection string should look like

    ip\SQLEXPRESS,1433

    Thursday, November 19, 2009 12:32 PM
  • User1327654160 posted

     I added the port number on my connection string, it gives the same error message.  This is my connection string on web.config file.

    <add name="XXXConnectionString" connectionString="Data Source=IP_address\instance_name,1433;Initial Catalog=database_name;Persist Security Info=True;User ID=username;Password=password"
       providerName="System.Data.SqlClient" />

    Thanks,
    Monica

    Thursday, November 19, 2009 1:23 PM
  • User1327654160 posted

    Hi,

    What else can I try or test the connection string?  Which part is failing, network, sql server connection or the ASP .net application connection string?

    It also give the same error when I use this connection string in web.config file:

    <connectionStrings>
      <clear />
     <add name="xxxConnectionString" connectionString="Data Source=IP_ADDRESS\INSTANCE_NAME,1433;Network Library=DBMSSOCN;Initial Catalog=databaseName;Persist Security Info=True;User ID=username;Password=password"
       providerName="System.Data.SqlClient" />
    </connectionStrings>

    Thanks!

    Friday, November 20, 2009 10:41 AM
  • User1327654160 posted

    Hi,

    I have another connection string connect the ASP role membership provider.

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

    Do I need to change it to the remote connection by using IP address?  Will this causes my problem?

    Thanks,
    Monica

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, November 20, 2009 12:19 PM
  • User1327654160 posted

    Finally the connection problem is resolved. 

    The default port 1433 was using a “globally defined” dynamic port. After changed it to a specific port pointing to my server.  It works fine.

    Friday, November 20, 2009 7:22 PM
  • User2130758966 posted

    Oh well I am just glad you solved it, these kinds of errors can be horrible to solve and you are basically on your own because its different every time!



    Monday, November 23, 2009 7:09 PM