Unable to create connectionstring for a remote desktop for a C# application

Question

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?

Sair

I checked that in the Configuration Manager, tcp-ip was enabled. – Sair

Remote connections to SQL Server Express are disabled by default - did you explicitly enable those? – Motoring

Yes, i explicitly enabled those tcp-ip protocols. – Sair

Answer 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>"

Autodidact

Answer 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.

Ejaculate

1. Open SQL Server Management Studio. 2. Right-click your server's name and select Properties. 3. Tick the checkbox Allow remote connections to this server. 4. Select OK. it is already done – Sair

Is there something more to do ? – Sair

Thank you for clarification – Ejaculate