Can’t access my SQL Server via C# form application from client device

by

Start with the following to troubleshoot why you’re not able to connect to your SQL Server.

Open PowerShell and run the following to check if SQL Server is listening on a TCP/IP port:

Note: The command is from this post

ForEach ($SQL_Proc in Get-Process | Select-Object -Property ProcessName, Id | Where-Object {$_.ProcessName -like "*SQL*"})
{
    Get-NetTCPConnection | `
     Where-Object {$_.OwningProcess -eq $SQL_Proc.id} | `
      Select-Object -Property `
                                @{Label ="Process_Name";e={$SQL_Proc.ProcessName}}, `
                                @{Label ="Local_Address";e={$_.LocalAddress + ":" + $_.LocalPort }},  `
                                @{Label ="Remote_Address";e={$_.RemoteAddress + ":" + $_.RemotePort}}, State | `
      Format-Table
}

If it’s listening, you’ll see something similar to the following:

enter image description here

If not, it won’t return anything. In this case proceed to the next step.

Open SQL Server Configuration Manager

enter image description here

In SQL Server Configuration Manager, ensure TCP/IP is enabled for SQL Server. If not, enable it.

enter image description here

Double-click TCP/IP to open the properties window. Click on the “IP Addresses” tab. Scroll to the bottom. This is where you can change it from using “TCP Dynamic Ports” to using a specified TCP Port (ie: 1433)

enter image description here

If you’ve made any changes to the TCP Port, you’ll have to restart the SQL Server services (Control Panel => Administrative Tools => Services => SQL Server…). Alternatively, you can restart your computer.

Get SQL Server instance name

  • Open PowerShell, and type the following:
Get-CimInstance -Namespace Root\Microsoft\SqlServer  -Query "Select Name from __Namespace where Name like 'ComputerManagement%'" | ForEach-Object { $sqlMgmtVer = $_.Name; Get-CimInstance -Namespace Root\Microsoft\SqlServer\$sqlMgmtVer -Class FileStreamSettings |Select-Object InstanceName }

Alternatively, open a cmd window and type:

sc query type=service | find /i "sql"

Identify your local IP Address

  • Open PowerShell, and type the following:
 Get-CimInstance -Namespace Root\cimv2 -Query "SELECT Description, DHCPEnabled, DHCPServer, IPAddress, MACAddress FROM Win32_NetworkAdapterConfiguration WHERE IPEnabled=True" | Select-Object Description, DHCPEnabled, DHCPServer, IPAddress, MACAddress

Identify your public IP Address

Configure the Windows Firewall to Allow SQL Server Access.

Configure a Windows Firewall for Database Engine Access (shows how to configure the Windows firewall when using dynamic ports also)

Note: In addition to configuring the Windows firewall, it may be necessary to set up Port Forwarding in your router. Refer to the documentation for your router for more information.

Update

provider: Named Pipes Provider, error: 40 – Could not open a
connection to SQL Server

According to SQL Server clients may change protocols when the client computers try to connect to an instance of SQL Server

…if a client computer has both TCP and Named Pipes available, and
the order is:

  • TCP
  • Named Pipes

When the client computer tries to make a TCP connection to the server
and the connection attempt returns a non-zero return code, the client
transparently tries a connection by using the next protocol in the
list, which is Named Pipes…

The client does not receive an error that indicates the first protocol
failed.

If the client application uses the second protocol, and it also
returns an error, an error is returned to the client.

If you make an alias by using one of the following methods, the client
application uses the alias information to establish a connection to
the server and does not use any additional protocols
… If you want to control the protocol that a client application uses for every connection attempt, and not allow
the client to try multiple protocols, you can do one of the following:

Use the SQL Client Network utility or SQL Server Configuration Manager
to create an alias by specifying the protocol you prefer.

Specify the protocol in your connection string.

According to the connection string documentation, to specify that the connection only use TCP/IP, Network Library=DBMSSOCN; needs to be specified in the connection string.

Example:

Data Source=190.190.200.100,1433;Network Library=DBMSSOCN;Initial Catalog=myDataBase;User ID=myUsername;Password=myPassword;

See also Network Protocol for SQL Server Connection

According to Configure a Windows Firewall for Database Engine Access

The SQL Server Browser service lets users connect to instances of the
Database Engine that are not listening on port 1433, without knowing
the port number. To use SQL Server Browser, you must open UDP port
1434. To promote the most secure environment, leave the SQL Server Browser service stopped, and configure clients to connect using the
port number.
… As an alternative to configuring SQL Server to listen on a fixed port and opening the port, you can list
the SQL Server executable (Sqlservr.exe) as an exception to the
blocked programs. Use this method when you want to continue to use
dynamic ports. Only one instance of SQL Server can be accessed in this
way.

To see which SQL Server services are running:

Open PowerShell and type the following:

Get-Service | Where-Object { $_.DisplayName -Match "^SQL Server.*"}

Additional Resources

More Related Contents:

  1. C# Sql Textbox Select
  2. How to execute a stored procedure within C# program
  3. How to get the connection String from a database
  4. Must declare scalar variable @Id?
  5. How do I return multiple result sets with SqlCommand?
  6. Save byte[] into a SQL Server database from C#
  7. Getting return value from stored procedure in C#
  8. I get a “An attempt was made to load a program with an incorrect format” error on a SQL Server replication project
  9. SQL error: Incorrect syntax near the keyword ‘User’
  10. How to run multiple SQL commands in a single SQL connection?
  11. Is there any difference between DateTime in c# and DateTime in SQL server?
  12. Queuing in OneWay WCF Messages using Windows Service and SQL Server
  13. Multi threading C# application with SQL Server database calls
  14. ‘datetime2’ error when using entity framework in VS 2010 .net 4.0
  15. What SqlDbType maps to varBinary(max)?
  16. Received an invalid column length from the bcp client for colid 6
  17. How to pass XML from C# to a stored procedure in SQL Server 2008?
  18. How can I convert ticks to a date format?
  19. Entity Framework | Sequence contains more than one matching element
  20. When is it better to store flags as a bitmask rather than using an associative table?
  21. How to pass a null variable to a SQL Stored Procedure from C#.net code
  22. Keyword Not Supported: Metadata
  23. Why is EF generating SQL queries with unnecessary null-checks?
  24. Know when to retry or fail when calling SQL Server from C#?
  25. What’s the best way to bulk database inserts from c#?
  26. How to insert data into SQL Server
  27. SQL Server Database Change Listener C#
  28. Subset sum problem
  29. Should the order of LINQ query clauses affect Entity Framework performance?
  30. How do I connect to a SQL database from C#?

Leave a Comment