Unable to create connectionstring for a remote des

问题:

I want to create a Windows service which will copy data from one database to another database. The source database is in a remote location, below is the function using SqlConnectionStringBuilder and creating the connection string:

public string CreateConnectionString()
{
    SqlConnectionStringBuilder b = new SqlConnectionStringBuilder();
    b.DataSource = "ABCKOL-BCKOFF\\SQLEXPRESS";
    b.InitialCatalog = "netXs";
    b.IntegratedSecurity = false;
    b.UserID = "userid";
    b.Password = "password";

    return string connectionString = b.ConnectionString;
}

But unfortunately, it is showing an error like this:

Is there anything I should know more/check more?

回答1:

Try to do telnet localhost 1433 from command prompt (from the same server where SQL services running), if the connection accepted without any error, then your SQL server really ready to accept connections. Otherwise follow these steps to troubleshoot the issue:

• Make sure TCP/IP protocol enabled
• Verify if the custom port configured (via SQL Server Configuration manager) for SQL Express service (as per screenshot)

If the custom port NOT configured

1. Make sure SQL Browser service is running
2. Create a rule in Windows Firewall to accept incoming connections on TCP ports 1433 and 1434 (TCP and UDP)
3. Restart SQL Browser service
4. Do telnet localhost 1433 again to verify

If the custom port configured

1. Create a rule in Windows Firewall to accept incoming connections on Custom TCP ports
2. Restart SQL service
3. Do telnet localhost <custom port> to verify
4. Change the connection string to DataSource = "ABCKOL-BCKOFF,<custom port>"

回答2:

Check if the server itself is available and if the required instance of MS SQL Server is available through SSMS. It is possible that ports for remote access to MS SQL Server are not enabled or the server itself is unavailable.