Remote Connection for SQL 2008 R2 Express?

Follow this step by step guide to get remote connections enabled properly:

http://www.linglom.com/2009/03/28/enable-remote-connection-on-sql-server-2008-express/

Thanks for the link, but I've done all those.  Any other ideas?

Can you post the error message you are encountering so that we can try fixing it accordingly..

A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)

Ok, Kindly confirm the following:

1. Whether your instance is default or named one
2. If Named, whether Browser service is up and running
3. Able to ping SQL Server machine from client machine
4. If pingable, then try the below one

telnet sqlservername 1433

it should open up a blank window, if not then you have some network connectivity issues

Note: You should have restarted SQL Server services after enabling Remote connections to get it work..

Are you able to ping your Win7 machine from remote machine? If not, Turn ON "Network Discory" and "File and Print sharing" in Networking and Sharing Center. Make sure you're able to ping your SQL server machine.

After that, create a udl file on any other machine from which you are trying to connect your SQL Server. Create a txt file and change it's extension to udl.
 - Open udl file
 - In Provider tab, select Microsoft OLE DB provider for SQL. Click Next
 - In Server Name drop down box, see if you're able to see the SQL instance.

maybe you should check your filewall, make sure sql server does not blocked by it.

firewall.

rrjegan17:

I made sure to restart the sql server and browser services, and even rebooted just to be sure.

1. The instance is a custom name instead of default SQLEXPRESS.

2. Browser service is set to Automatic and is up and running (best I can tell, anyway).

3. Able to ping using both PC name and IP, all other folder shares, printer shares, etc. work fine.

4. When trying to telnet using telnet computername 1433 , I get error message: "Connecting to computername...Could not open connection to the host, on port 1433: Connect failed".  I also tried telnet from the server itself and it would not connect using computername, localhost or the ip.

put ip address in the sql server name field. this will escape the dns

what is in the sql event log?

shahbaz-ahmad:

Made the udl file.  In providers tabIe selected Microsoft OLE DB provider for SQL.  On Connection tab my servername shows up.  I entered in "use a specific user name and password"  Entered in information for user sa.  When I went to "select the database on the server" I get the following error:

[DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied.  Then the next box after I click OK says "Login failed. Catalog information cannot be retrieved."

timeexist:
I currently have my windows firewall turned off for testing.

can you get access sql server in localhost\SQLEXPRESS method with udl?
if you can, sql server is running, if not sql server is not running.

Timexist:
OK, I just connected fine using the .udl method on the server itself.  The servername\instancename pops up immediately.  When I enter in the login info I see the database in the list.  When I test connection is says succeeded.

The last time I looked at the SQL event log I did not see anything related to a remote machine trying to connect and failing.  I don't remember where to find the log now.

There's got to be some little switch somewhere that I must have accidentally turned off/on, arg!  If it makes any difference, I used to have SQL 2005 Express and SQL 2008 Express installed on this machine.

If it makes a difference, my instance is a custom name, not the default SQLEXPRESS

We separate the issue into two parts: sql server problem or network communication problem.

1, so first make sure sql server is running. check service to make sure the sql server (instance name) is running.
I think the sql server is running.


2, check sql server configuration manager,
<<sql server network configuration>>
protocols for sqlexpress--- tcp/ip enable
right click -->properties---> ip address setup port 1433 to the right ip address

>> If it makes a difference, my instance is a custom name, not the default SQLEXPRESS

Ok, this is what I wanted to confirm..
And are you sure that this named instance is using default TCP port 1433..
If not, then use the correct port..

server side:
if you in the domain network, you can setup sqlexpress account use your domain username and login.

client side:
in your udl file, you can use windows NT intergated sceurity. it will work.

tcp port is not very important here.
user login is the key here.
for some reasons, sa account is disabled on my windows 7.
so if you can use network login set up an account, then it will work.

So you have tested successfully your SQL connection using udl on server itself. have you used sa username and password? If yes, that means SQL is running and there is no issue in the sa username authentication on the local server.

Now open SQL Server Configuration Manager and expand SQL Server <version> network configuration in the left tree and click on Protocols for MSSQLSERVER.
In the details pane, enable three (3) protocols except VIA. You can see IP/Port in TCP/IP properties if you want but don't not change anything. After enabling protocols, click on SQL Server Services in the left tree and restart all the services.
Now try to connect using udl from any machine other than server and let me know.

The SQL server is working fine locally.  I can connect no problem using user sa.  My program I am using locally also works as expected.  It's as if there is some network component that is not working, or not turned on.  I have attached screenshots to show a little bit of my setup
sql-server-services.jpg
sql-protocols.jpg
sql-remote.jpg

Yes, the article you got that from was a help, I've already done those steps.  My machine is windows 7, so doesn't match exactly.  I also have turned off the firewall for the testing... even though it is acting like it is still on.

Here is what I have:
sql-firewall-settings.jpg
sql-firewall.jpg

OK. When you make udl on other system, can you see the SQL instance in the drop down list?
If yes then your SQL is available on the network and there is no network issue.

The only thing that showed in the udl drop down list was the computer name Brian-PC.  It did not show the instance also, nor was it able to connect.  When I did the udl on the local machine, it showed Brian-PC\FINAWARE and everything works correctly.

The weirdest thing of all, is it used to work, then it stopped working all of a sudden (maybe I installed a program that broke it, I don't remember).  Since then I've tried many different things.  I used to have SQL 2005 Express and SQL 2008 Express installed.  I uninstalled both of those and installed SQL 2008 R2 Express instead.  Maybe there is remnants of the other SQL versions causing problems?

Brian-PC\FINAWARE instance name is the different name or it was used before for those instances also which you had uninstalled?

I'm not sure I understand your question.  I believe there was a SQLEXPRESS instance at some point, but it's pretty much been FINAWARE since the start.  The program even installed SQL Express 2005 before I had any other versions.  This instance of FINAWARE is brand new as I just installed SQL Express 2008 R2 the other day after uninstalling the other two versions of MS SQL.

If you restart your sql browser with sa account, I think you should be able to access it with sa account through network. Your current sql browser is using local service account, maybe this is the reason you cannot link to it through network.

here is the solution, sa account only belong to sql server,
and I think sa account has been isolated inside windows 7 localhost only.
so if you want to use a user specific login, it has to be network user login.

I think you should install management stdio express, in security add the network user login. then use windows NT integrated security to check in.

I think this is the only way to run network sql 2008 express.

no need to setup port 1433, what you need is a network account in sql.

I've given up after trying everything you guys mentioned.  I uninstalled 2008 R2 Express and reinstalled 2005 Express.  It worked right away.  Everything worked fine locally with 2008 R2, it just wasn't allowing any network connections.

I don't think the TCP Dynamic ports had anything to do with it because there is an option in the properties that says "listen on all ports".  But I tried setting them to 1433 anyway and turning dynamic to blank.

User authentication (sa vs. windows auth) wasn't the problem since any remote machines couldn't begin to connect to the instance since they couldn't see it.

I even tried to use the "upgrade" feature in 2008 R2 after I got everything working with 2005 and I got a "can't open key" permission error and followed instructions on the net to give permission, but didn't work.  I have already spent too much time on this and can't afford to waste more.  THANK YOU EVERYONE for trying to help, you guys really gave a great effort.  If I need to use server 2008+ for something, I will have to revisit this issue, but for now everything is working.

Unless anyone has any last minute ideas, I'm going to close the question and award multiple solutions shortly.

rrjegan17, i set all the IPs in the TCP settings to blank Dynamic and 1433.  If I did that wrong, please let me know.