Sql Server 2008 R2 connection error

Question

I have installed Sql Server 2008 R2(express edition) on my server and now I want to connect to that server from my client.

But it shows the following error:

cannot connect to SMSERVER\SMTECH

How can I solve this?

Answer 1

If you can connect locally but you cannot connect remotely there are a few things that you can try.

Windows Firewall
You might need to make an exception in Windows Firewall on the server to allow for connections. You need to open ports 1433 and 1434 by default. Obviously, this changes if you've changed the default ports.

Enable TCP/IP connections
Open SQL Server Configuration Manager and select your instance from Network Configuration and then enable TCP/IP as illustrated in the image below.

Check other firewalls between client and server
If both of those look good, then there is probably something else blocking the ports between the client and the server. Inspect the policies on any ACLs between the two and ensure that the necessary ports are allowed to reach their destination.

Answer 2

Due to my experiences with Microsoft SQL Server there are two things that might have to be set up to get SQL Server work with clients:

1. Firewall on Server Site The newer Microsoft OS (Server and Client) have the Microsoft Firewall preinstalled and activated. So if you didn't open the appropriate Firewall port this would be the problem. There is a article in Microsoft Technet on how to configure the firewall: http://technet.microsoft.com/en-us/library/cc646023.aspx

2. Protocol settings for SQL Server If your client program uses a protocol which is not activated for the SQL Server instance, than you also get a connection error. To show the protocols which are activated and to control them use the Microsoft SQL Server Configuration Manager utility. More details on this topic can also be found in Microsoft Technet: http://technet.microsoft.com/en-us/library/ms345343.aspx