Solved

Remote Connection for SQL 2008 R2 Express?

Posted on 2010-09-06
33
4,720 Views
Last Modified: 2012-05-10
Trying to access my database on another computer on my LAN.  I have MS SQL 2008 R2 Express installed in Windows 7 64-bit.

I have the following settings:
- both Windows Authentication and SQL Server Auth Enabled
- SQL Server Browser service running (Automatic) and "Log On As" LocalService

I have tried the following:
- Verified "Allow Remote Connections" is enabled in Server Management Studio
- tried to connect with both the direct IP and server computer name on client machine
- In Server Config Manager, Enabled all protocols for Database
- Also in Protocols: TCP/IP properties Enabled all IP Addresses
- Allow TCP Port 1433 through Firewall
- Allow UDP Port 1434 through Firewall
- Allow SQL Browser EXE through firewall
- Allow SQL Server EXE through Firewall
- Even tried with Firewall turned off

I've been combing google for hours, and still no luck.  I am able to connect and use the database locally on the server, just not with any other client machine on the network.

Please help!

Thanks,
-brian
0
Comment
Question by:BossTurbo
  • 12
  • 11
  • 5
  • +1
33 Comments
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 33614691
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/
0
 

Author Comment

by:BossTurbo
ID: 33614715
Thanks for the link, but I've done all those.  Any other ideas?
0
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 33614720
Can you post the error message you are encountering so that we can try fixing it accordingly..
0
 

Author Comment

by:BossTurbo
ID: 33614799
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)
0
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 33614811
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..
0
 

Expert Comment

by:shahbaz-ahmad
ID: 33614866
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.
0
 
LVL 4

Expert Comment

by:timexist
ID: 33614942
maybe you should check your filewall, make sure sql server does not blocked by it.
0
 
LVL 4

Expert Comment

by:timexist
ID: 33614944
firewall.
0
 

Author Comment

by:BossTurbo
ID: 33614946
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.
0
 
LVL 4

Expert Comment

by:timexist
ID: 33614949
put ip address in the sql server name field. this will escape the dns
0
 
LVL 4

Expert Comment

by:timexist
ID: 33614954
what is in the sql event log?
0
 

Author Comment

by:BossTurbo
ID: 33614977
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.
0
 
LVL 4

Expert Comment

by:timexist
ID: 33615068
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.
0
 

Author Comment

by:BossTurbo
ID: 33615096
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.
0
 

Author Comment

by:BossTurbo
ID: 33615097
If it makes a difference, my instance is a custom name, not the default SQLEXPRESS
0
 
LVL 4

Expert Comment

by:timexist
ID: 33615137
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

0
Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 33615164
>> 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..
0
 
LVL 4

Expert Comment

by:timexist
ID: 33615178
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.
0
 
LVL 4

Expert Comment

by:timexist
ID: 33615199
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.

0
 

Expert Comment

by:shahbaz-ahmad
ID: 33616045
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.
0
 

Author Comment

by:BossTurbo
ID: 33620476
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
0
 

Assisted Solution

by:shahbaz-ahmad
shahbaz-ahmad earned 166 total points
ID: 33622128
If you are able to ping then your Server is on the network. Ok, try this now.

Open up Windows Firewall and select Change Settings, select the Exceptions Tab and click Add Program. You'll need to select the SQLServer.exe in Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\Binn\ and the SQLBrowser.exe in Program Files\Microsoft SQL Server\90\Shared\. Then select Properties for each of them and select the "Change Scope" button.

See attached file for screenshot. NOTE: location of the above mentioned programs could be different depending on your installation.


Firewall.bmp
0
 

Author Comment

by:BossTurbo
ID: 33622542
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
0
 

Expert Comment

by:shahbaz-ahmad
ID: 33622994
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.
0
 

Author Comment

by:BossTurbo
ID: 33623090
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?
0
 

Expert Comment

by:shahbaz-ahmad
ID: 33623242
Brian-PC\FINAWARE instance name is the different name or it was used before for those instances also which you had uninstalled?
0
 

Author Comment

by:BossTurbo
ID: 33623901
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.
0
 
LVL 4

Expert Comment

by:timexist
ID: 33623964
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.
0
 
LVL 4

Expert Comment

by:timexist
ID: 33624154
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.

0
 
LVL 57

Accepted Solution

by:
Raja Jegan R earned 167 total points
ID: 33624486
BossTurbo,

As per sql-protocols.jpg, your instance is listening on Dynamic ports ( TCP Dynamic Ports = 0) and not on a standard or default port 1433 and that's why excluding TCP IP port on Firewall didn't helped out in your case.
Configure it as mentioned below:

TCP Dynamic Ports :  Set it to Blank and remove 0
TCP Port : Set it to 1433 or some other TCP Port

Restart SQL Server services and after that you would be able to connect your SQL Server instance from Client machine on the specified port.
0
 

Author Comment

by:BossTurbo
ID: 33630664
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.
0
 

Author Comment

by:BossTurbo
ID: 33630697
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.
0
 
LVL 4

Assisted Solution

by:timexist
timexist earned 167 total points
ID: 33632516
Yeah, I think uninstall and reinstall sql sever 2008 R2 really did the job, same thing happened  on my Windows 7.
In the network environment, you don't have to "see" the remote machine in the dropdown list, you can just type in the remote server name like "Brian-PC\FINAWARE" in the field. Your machine boardcasting a message to the network, only the machines responded in time will on the list.


On my machine, it had a sa account disabled problem,  I fixed it with uninstall and reinstall sql 2008 R2, I believe it is a permission problem in Windows 7.
also I think sql server 2008 express R2 is different from sql 2008 express. upgrade cannot fix the problem.
I have Visual Studio 2010 professional installed with SQL server 2008 express, it worked with windows authentication only mode by default, and it can be accessed by remote windows authentication mode. BTW I always type in server name.

So basically I think, if we install SQL server 2008 R2 in mix authentication mode at the beginning, then both sa and windows authentication mode can have remote access.


The only setup needed is starting SQL server when you want it.

I am glad you can resolve the problem by yourself.

have fun.
0

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now