CODEWITHSUNDEEP
asp.netsql-servernetwork-programminginfrastructure
Hakan Zim
Hakan Zim

Reputation: 305

sql server 2012 allowing remote connection

I want to connect asp.net application hosted at a web server with sql server 2012 db hosted at another remote location. I have enabled the TCP/IP on sql server, also set port 6322 for ip1 and ipall. but if i go to my web server and telnet into the db server, no connection is established. in ASP.net page just to test the connection i am using the connection string as below 

 <add name="DBConnectionStringTEST" 
 connectionString="Data Source=<db ip address>; Initial Catalog=<db name>; Persist Security Info=True;User ID=<username>;Password=<password>;connection timeout=0;Max Pool Size = 100;Pooling = True" 
      providerName="System.Data.SqlClient" />

my questions,

1) is there something i am missing, like wrong port.

2) do i need to give db ip address(client_net_address) or the actual db server address.

3) do i need to ask the server administrator to allow web server ip address to connect.

4) what is the quickest way to test if the web server can talk to the sql server without having to amend the connection string in asp.net application every time i make changes.

NOTE: i have searched other similar topic but couldnt find any answer which worked for me.

Upvotes: 1

Views: 798

Answers (2)

Rahul
Rahul

Reputation: 77926

1) is there something i am missing, like wrong port.

From your posted connection string it looks like you are using default instance. if it's indeed named instance then provide the same info in your connection string.

2) do i need to give db ip address(client_net_address) or the actual db server address.

It should be actual DB server IP address/Hostname along with Db server installed instance name.

3) do i need to ask the server administrator to allow web server ip address to connect.

If you are providing correct IP addr/hostname/port/credential then it should allow you to connect through. (provided your DB admin have already created DB user for you)

4) what is the quickest way to test if the web server can talk to the sql server without having to amend the connection string in asp.net application every time i make changes.

By creating a UDL file. Open a notepad and save it as test.udl. Once saved -> double click the file -> provide all info and test the connection. It should look like below

enter image description here

EDIT:

To configure a universal data link (.udl) file

  1. Double click on he file named test.udl

  2. On Provider tab select SQL Server Native client 10.0

  3. On Connection tab

    enter BRR=SQL-N1 as Server Name

    In Username and password section provide SA and password

    Click on Test Connection

See Here for more information on this.

Upvotes: 1

Dan Guzman
Dan Guzman

Reputation: 46281

Without an explicit port or instance name specification, the default 1433 port will be used. Specify your custom port in the connections string:

<add name="DBConnectionStringTEST" 
    connectionString="Data Source=<db server ip address or host name>,6322; Initial Catalog=<db name>; Persist Security Info=True;User ID=<username>;Password=<password>;connection timeout=0;Max Pool Size = 100;Pooling = True" 
      providerName="System.Data.SqlClient" />

An easy way to test network connectivity is with TELNET:

TELNET <db server ip address or host name> 6322

This will show an empty console window if successful. Press ctrl-C to close.

You can also verify port connectivity with the Powershell command below if you don't have TELNET installed:

6322 | % { echo ((new-object Net.Sockets.TcpClient).Connect("<db server ip address or host name>",$_)) "server listening on TCP port $_" }

Upvotes: 1

Related Questions