Connect over WiFi to SQL Server from another computer

sql-server-2008

I tried to connect over WiFi to SQL Server with SQL Server Management Studio from another computer, but it failed.

I have a computer with Windows 7 & SQL Server 2008 (lets say the server computer). Next to it I have a freshly installed computer with Windows 7 & SQL Server Management Studio (let's say the client computer).

What I did on the server computer:

  • Configure firewall by enabling port 1433
  • Enabled network protocols (TCP/IP) inside SQL Server Configuration Manager
  • Checked Allow remote connections to this server in server properties in the SQL Server Management application.
  • Started SQL Server Browser
  • Restarted services

(SQL Server Browser is stopped at this point, but I don't think it is necessary. Is it?)

Next, I successfully tested a ping on the port 1433 from my client computer with a tool named tcping (ex: tcping 192.168.1.4 1433). But I still cannot connect from my client computer to SQL Server on my server computer.

Ok, something new with this problem: Until now, I successfully connected to my "server computer" with Management Studio. What I did is type the computer name in the server name field in the connection window of Management Studio. My previous (failed) attempt was to type the computer name followed by the instance of SQL server (ex: COMPUTER_NAME\SQL2008). I don't know why I only have to type the computer name.

Now my new challenge is to be successful in connecting my VB6 application to this remote database located on my "server computer". I have a connection string for this but it failed to connect.

Here is my connection string:

"Provider=SQLOLEDB.1;Password=mypassword;User ID=sa;Initial Catalog=TPB;Data Source=THIERRY-HP\SQL2008"

Any idea what's going wrong?

Best Answer

First, the way to address a specific SQL server instance is: SERVERNAME\INSTANCENAME

So, first step is to see if you can resolve SERVERNAME (use ping or change it for an IP address).

The second step is to see if the specific instance exists. Typically, the first instance of MSSQL server is using the default instabnce name (. or MSSQLSERVER). If that's the case, you can omit the instance name in your connection string and keep the server name only.

To check your instance name, open the service manager on the server and find the "SQL Server (xxx)" service. The "xxx" will be the instance name.

Once you've done that and made sure you're using the correct SQL address, you should validate the connection. Check if both your machines are on the same subnet. If they aren't, make sure the firewall isn't set to only allow the local subnet to connect (typical in "home" network profile).

Then, if both machines are on the same WIFI network, make sure your AP isn't setup for client insulation. If that's the case, you won't be able to communicate between two WIFI devices connected to the same AP.

Finally, make sure you have a login that works. The simplest way to do that is to create an explicit login on the SQL server, give it all necessary rights on your database and server and test it locally by passing the credentials in the command string. If it works, try it from the remote machine

Related Topic