SQL Server on remote computer

Question

I have a SQL Server Express instance on a computer with IP 192.168.66 on a home network.

I have enabled TCP/IP on port 49170 and added the incoming rule on Windows firewall.

I have an application that I want to install on different computers inside my network. When I am testing my application in Visual Studio, I am not able to connect to my instance on the remote computer.

<connectionStrings>
    <add name="EquipamentoDal.Properties.Settings.ExtinguisherMngConnectionString"
         connectionString="Data Source=192.168.1.66,49170;Initial Catalog=ExtinguisherMng;Integrated Security=True"
         providerName="System.Data.SqlClient" />
 </connectionStrings>

The server name is DESKTOP-HU51203\EXPRESS. What i have done wrong to not be able to connect?

Answer 1

Your connection string does not specify the Named Instance you're connecting to, it only specifies the IP address, which means any clients will connect to the default instance instead of a named instance.

By default SQL Server Express is configured differently to 'standard' versions of SQL Server (Standard, Workgroup, Developer, and Enterprise Editions) as follows:

• Block incoming remote connections - this must be explictly enabled in the SQL Server Configuration Manager
• Use a Named Instance called SQLEXPRESS instead of being configured as the Default Instance
• SQL Server Express is also unique in supporting AttachDb and LocalDb - the other SKUs/Editions do not support the AttachDb and LocalDb features.

In your case, you've already enabled Remote Connections and you're not using AttachDb or LocalDb, so you just need to configure your client to connect to a named instance:

You specify the named instance in your connection string using the format <host>\<instance name>,<port> in the Data Source= or Server= parameter, e.g. Data Source=foo\SQLEXPRESS,1433, or in your particular case:

Data Source=192.168.1.66\SQLEXPRESS,49170;Initial Catalog=ExtinguisherMng;Integrated Security=True

You mentioned that you're doing this on a Home network, which means it's unlikely you're using an Active Directory or Kerberos, and given SQL Server Express does not expose the passwords it uses for its Service Identity (nor can users authenticate as NT AUTHORITY\NetworkService over the network it means you cannot use Integrated Security=True (or Integrated Security=SSPI which is equivalent, but SSPI is the preferred value), you will need to set an explicit SQL Server Login's username and password in your Connection String.

1. Create a new Login object in the Server (you will need to login to the server first somehow, either locally or remotely using the sa account, if it exists)
2. Specify the new Login's credentials by replacing Integrated Security=True with User Id=<userName>;Password=<password>;