Login to SQL Server 2008 R2 remotely

Question

0.00/5 (No votes)

See more:

SQL-Server-2008R2

Hello All

It seems this is a common problem over the internet so let me give you details on my environment and the steps I did:

Client - Windows 7 32-bit with SQL Management Studio
Server - Windows Server 2008 R2 with SQL Server 2008 R2 [Default not named instance] and SQL Management Studio
For the server I added Application server role. I think I also added Web server role. The server and client are on the same LAN.

After installation and testing windows authentication to the SQL server, this is -in short words- what I did to enable SQL server remote login:
1 - Enabled and started all SQL Server services.
2 - Enabled TCP/IP for SQL Server. [I restarted the server to be sure after this step]
3 - Enabled TCP port access on the firewall.
4 - Allowed sqlservr.exe and sqlbrowser.exe on the firewall.
5 - Created another user rather than sa and give it sysadmin role.

On the client machine, when I open SQL management studio, I tried to login with server IP address, server name, server name or IP with port [192.168.1.25,1443]. Username and password are correct.

After the previous explanation, I can't access the server's SQL Server remotely, I get the famous error "A network-related or instance specific". Notice that the same error message appear, but with different error codes after performing some steps from the above. I can't provide you with the error codes since I'm not with the machine right now. Any help is really appreciated! Thanks!

Posted 18-Jan-15 9:00am

Abdallah Al-Dalleh

Add a Solution

Comments

RedDk 18-Jan-15 15:05pm

I can tell you this:

Once the Adminstrator privilege is authorized for the user from the remote machine, ON THE REMOTE MACHINE, the instance will be valid and accessible from that remote machine. The path, along with the computer name, will be available in the log-on dialog when you go to start the server.

Simple as that.

And as a general rule, perhaps this is just a personal preference that I have come to trust after jumping through too many hoops attempting to get ann instance running through IP addressing using the LOCAL account, and failing, try not to use the "local" account.

See there in that dialog how easy it is to use named instance.

Also perhaps "see" how Visual Studio SQL Server handles waht it is you think is a database. Use all the tools you have at your disposal.
[Edit]
forget default
[Edit]

Abdallah Al-Dalleh 18-Jan-15 15:28pm

Can you give me an example login based on my environment ?

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Wendelius · Answer 1 · 2015-01-18T17:34:00

Solution 1

Few things you should check

Firewall checks, if you reconfigured a separate firewall also check that the firewall on the server computer is configured to allow the connections

from the SQL Server configuration check that TCP/IP is enabled and remote connections are allowed if you need them

check if the target is a named instance and ensure that you provide the instance name in the connection

Posted 18-Jan-15 17:34pm

Wendelius

Comments

Abdallah Al-Dalleh 19-Jan-15 1:32am

I made the necessary firewall rules [I even turned it off], I have a default instance and TCP/IP enabled. I did these procedures. Could it be something on the client side ?

Wendelius 19-Jan-15 3:52am

Of course the client side also affects. Why not test with another client computer. Also double check the connection string and the instance name.

PIEBALDconsult · Answer 2 · 2015-01-18T18:34:00

Solution 2

Shouldn't the port be 1433 ?

"
The default port for SQL Server is 1433. The port doesn't need to be 1433, but 1433 is the official Internet Assigned Number Authority (IANA) socket number for SQL Server.
"

http://support.microsoft.com/kb/287932[^]

Posted 18-Jan-15 18:34pm

PIEBALDconsult

Comments

Abdallah Al-Dalleh 19-Jan-15 1:33am

1433 is the port as appeared on the SQL configuration manager.

PIEBALDconsult 19-Jan-15 9:46am

In your question you state 1443.