Enable Remote Connection on SQL Server 2008 Express

March 28, 2009 IT Support 336 Comments

Last time, I wrote an article show how to enable remote connection on SQL Server 2005 Express. Now SQL Server 2008 Express is released for a while, it doesn’t allow remote connection on default installation as on SQL Server 2005 Express. So you have to enable it manually. If you’re trying to connect to SQL Server 2008 Express remotely without enable remote connection first, you may see these error messages:

“Cannot connect to SQL-Server-Instance-Name
An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 28 – Server doesn’t support requested protocol) (Microsoft SQL Server)”
“Cannot connect to SQL-Server-Instance-Name
An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 26 – Error Locating Server/Instance Specified) (Microsoft SQL Server)”
“Cannot connect to SQL-Server-Instance-Name
Login failed for user ‘username‘. (Microsoft SQL Server, Error: 18456)”

To enable remote connection on SQL Server 2008 Express, see the step below:

Start SQL Server Browser service if it’s not started yet. SQL Server Browser listens for incoming requests for Microsoft SQL Server resources and provides information about SQL Server instances installed on the computer.
Enable TCP/IP protocol for SQL Server 2008 Express to accept remote connection.
(Optional) Change Server Authentication to SQL Server and Windows Authentication. By default, SQL Server 2008 Express allows only Windows Authentication mode so you can connect to the SQL Server with current user log-on credential. If you want to specify user for connect to the SQL Server, you have to change Server Authentication to SQL Server and Windows Authentication.

Note: In SQL Server 2008 Express, there isn’t SQL Server Surface Area Configuration so you have to configure from SQL Server Configuration Manager instead.

Step-by-step to enable remote connection on SQL Server 2008 express

Open SQL Server Configuration Manager. Click Start -> Programs -> Microsoft SQL Server 2008 -> Configuration Tools -> SQL Server Configuration Manager.
On SQL Server Configuration Manager, select SQL Server Services on the left window. If the state on SQL Server Browser is not running, you have to configure and start the service. Otherwise, you can skip to step 6.
Double-click on SQL Server Browser, the Properties window will show up. Set the account for start SQL Server Browser Service. In this example, I set to Local Service account.
On SQL Server Browser Properties, move to Service tab and change Start Mode to Automatic. Therefore, the service will be start automatically when the computer starts. Click OK to apply changes.
Back to SQL Server Configuration Manager, right-click on SQL Server Bowser on the right window and select Start to start the service.
On the left window, expand SQL Server Network Configuration -> Protocols for SQLEXPRESS. You see that TCP/IP protocol status is disabled.
Right-click on TCP/IP and select Enable to enable the protocol.
There is a pop-up shown up that you have to restart the SQL Service to apply changes.
On the left window, select SQL Server Services. Select SQL Server (SQLEXPRESS) on the right window -> click Restart. The SQL Server service will be restarted.
Open Microsoft SQL Server Management Studio and connect to the SQL Server 2008 Express.
Right-click on the SQL Server Instance and select Properties.
On Server Properties, select Security on the left window. Then, select SQL Server and Windows Authentication mode.
Again, there is a pop-up shown up that you have to restart the SQL Service to apply changes.
Right-click on the SQL Server Instance and select Restart.
That’s it. Now you should be able to connect to the SQL Server 2008 Express remotely.

336 Comments

Robert April 2, 2009

Thank you so much for publishing “Enable Remote Connection on SQL Server 2008 Express “. It was exactly what I needed.

Cheers,
Robert
Aju Peter April 7, 2009

Thanks!. It worked for me. JUST START “SQL SERVER BROWSER” service!.

There are two things I learned
1. SQL server 2008 does not have much to do with SQLServerAgent.
2. ServerBrowser is instrumental in connecting to SQL Server from remote machines (FROM LOCAL MACHINE THIS IS NOT REQUIRED)
Matt April 8, 2009

Thank you very much for putting this tutorial together. It has been very helpful!
Ronald April 22, 2009

Thanks!
SGR April 22, 2009

Thanks!
This tutorial helped me to connect to SQL Server 2008 Express from MS Access 2003 via ODBC. Now I can use MS Access as a front end to SQL Server 2008 express.
Richard April 30, 2009

Thanks!

This fixed the problem i had. I have been running SQL Server since October and for some reason i just couldn’t login this morning. Perhaps something else has changed on my PC??

Richard
Jason May 5, 2009

This tutorial is great!

Your instructions are thourghly written and should have assisted me in establishing the connection, but I keep getting the following:
Login failed for user â€˜usernameâ€˜. (Microsoft SQL Server, Error: 18456)

Ultimately, I am trying to use my local SQL Server Management Studio to connect to the remote SQL Server 2008 Express instance provided by my web host provider.

What could I be missing?
Sakoda May 7, 2009

It’s so great and very helpful!
linglom May 12, 2009

Hi, Jason
There may be a login problem. You should observe error state in the error message. See the post below for more detail.
Understanding “login failed” (Error 18456) error messages in SQL Server 2005
eiad May 23, 2009

You are a good person. Thanks so much!

Eiad
Jampa June 4, 2009

Thanx a lot mate, your tutorial helped me a lot…
Vadim June 4, 2009

Thanx man, it was very helpfull for me
keep the same way
Ben June 15, 2009

Fantastic – you have saved me a lot of time – many thanks!
Thurein June 17, 2009

Thanks for this post. It is really helpful.
Azec June 18, 2009

Hallow.

I did all you post, and also add ports 1433 + 1434 to firewall… and it is not workin, i tried near 2 days (( nothing changes. it drops 10060 Sql Server Excheption
MrGreek June 24, 2009

Hello.. I have tried for some while now to get my SQL Server Browser running but whit no luck…
Then I saw your site and i se that I have all ready tried all the things correct.. but when IÂ´m trying to change to Local Service under Built in account… then I get som errors!!
CanÂ´t start. No active units are associsat with it… (ore somethings like that.. do you have any suggastions? I donÂ´t know what to do now! HELP
linglom June 25, 2009

Hi, Azec
The error 10060 indicates that the client cannot open a socket to the SQL Server. You can verify the connection to the server by using telnet command with port 1433.

I think the problem could be a firewall or some hardware is blocking the traffic.

Hi, MrGreek
Can you show the exactly error message?

What if you specify other user account (domain users, network service)? Does the error message still showing?
Geraint June 30, 2009

Spot on! Three hours of messing around and searching Microsoft’s site. This is the best reference on the web at the moment for enabling remote access on SQL Server 2008.
Pooya Zandevakili July 2, 2009

Thanks so so much 🙂
Renee Kraft July 9, 2009

Thank you so much! Your posting was a huge help.
Marius July 21, 2009

Thank you for both the 2005 and 2008 guide! Helped me with both as we have both at work!
ig4 July 23, 2009

check win server 2008 firewall public profile state =off
start–>Administrative Tools–>Windows Firewall with Advanced Security, to click Properties,to select Tab “Public Profile” set firewall state =off.
Don’t need restart machine.
jeff August 7, 2009

excatly what i needed. you are awesome.
Engin August 9, 2009

Thanx to Turkey
fer August 15, 2009

Thanks. It is very helpful.
Serdar August 19, 2009

Thanks so so much to Turkey
IS Thakur August 23, 2009

Thanks a lot for such a valuable info.
matthew August 28, 2009

WOW! thank you, thank you, thank you!!
Ken September 8, 2009

This is such a great write-up. Thanks!! so much for taking the time to pull this together.
ali September 14, 2009

Thank you so much!
sayoni October 7, 2009

Thanks a lot… it solved my issue…..
MAsk October 8, 2009

Very usefull thank you

Don’t forget to use connection format :
“SERVER_IP\SQLEXPRESS”
When asked server adress or creating ODBC connection

also (for me) in step 6 :
“Protocols for SQLEXPRESS.” make sure not to be in “native client” section because TCP/IP is runnning by default.
rctaubert October 18, 2009

I have followed your outline above to the letter.

On WHS server I have set the Firewall…Exceptions…Add Programs:

SQL Server Error and Usage Reports
SQL Server Management Studio
sqlbrowser.exe (C:\Program Files\Microsoft SQL Server\90\Shared\sqlbrowser.exe)
sqlservr.exe (C:\Program Files\Microsoft SQL Server\MSSQL.10SQLEXPRESS\MSSQL\Binn\sqlservr.exe)

I added the port: SQLExpress 1533

The log shows no indication that the server even knows there is a logon attempt.

On my laptop using SQL Management Studio I go to the “Connect to Server” dialog box, click the down-arrow on the Server name box…Browse for more names…Network Servers tab.

In the text box SERVER1\SQLEXPRESS (10.0) appears. I click on it and click Ok.

I select SQL Server Authentication and enter the sa login and the password I set for this login.

It will eventually time out with the following error message:

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) (Microsoft SQL Server, Error: -1)

What am I NOT doing right????

On the server everything seems to work just fine.
linglom October 19, 2009

Hi, Rctaubert
Are you sure that the SQL Server is running on port 1533? By default, it is 1433.

I would suggest you temporary disable the firewall and retry connect to the SQL Server to see if the problem is at firewall configuration or not.
rctaubert October 19, 2009

I am sorry. I meant 1433. BUT, it turns out it was set to 1578. netstat -an show it was listening on that port.

If I use Server1\SQLExpress,1578 it works.

How would I get it back to 1433???
Shoikat October 25, 2009

Thanks.
It is very simple but very helpful.
J.Ackerman October 26, 2009

@No.2: even the _local_ SQL Server Express installation is dependent from the running SQL Server Browser service. When it’s not running then even on local machines you can not connect.
Anyway thanks for this help to enable connection!
linglom October 27, 2009

Hi, Rctaubert
You don’t need to change if it’s working properly. But if you really want, just open SQL Server Configuration Manager -> expand SQL Server Network Configuration -> click on Protocol for your SQL Server -> double click on TCP/IP -> Click on IP Addresses tab. On IPAll section, here you can change port value to the new one by modify the existing value. Then, restart the SQL Server service.
Sareesh November 6, 2009

Thank you for your nice and clean article.
Panwar November 19, 2009

Hi linglom,

This query is related to SQL server express 2005. By default if we install SQL server express 2005, it does not allow remote access to the created instance. Is there any way to allow remote connection to the created instance without using the UI (e.g. SQL Server Configuration Manager) i.e by changing some registry or invoking some exes? I am creating silent SQL instance (no UI).

TIA.
Jyothsna November 20, 2009

Hi,

Thanks a lot for this well explained tutorial along with screenshots. Appreciate your patience.
David November 24, 2009

Thank you!! I was trying to get some work done at home and I couldn’t figure out for the life of me why my sql server was not working (and it was working yesterday too…)
jim s November 29, 2009

Thanks! This was causing me much consternation.
Moonheart November 30, 2009

thx for this solution
but i have WMI provider error
plz how can i connect to SQL server 2008 in my PC
Gerry December 10, 2009

Thanks very much for the sql 2008 set up information it was very helpful and enabled remote connection. However, I cannot seem to get Microsoft Access to connect and my host company are using port 1334 instead of 1434 and MS access dosnt seem to allow me to change the port?
Help wouls be appreciated.
Regards
Gerry
joyhan December 14, 2009

Thanks a lot. It is very helpful!
imtiaz December 16, 2009

after your setting cannot my native client and sql service start

solve my problime
Abdiel December 18, 2009

Thanks! I was about to reinstalll the whole sql 2008.
KMBrewer December 19, 2009

THIS IS THE ONLY FIX FOR THIS ISSUE -PERIOD!

Thanks so muchmy friend this has really been a headache for me. After 3 install/uninstall attempts nothing was working for me. Again, thanks for the detailed resolution to this anomaly.
KMB
linglom December 30, 2009

Hi, Moonheart
Can you show content of the error message?

Hi, Gerry
You can try to connect to the SQL Server using SQL Server Management Studio from a remote PC to see if the SQL Server is configured correctly.
ALI December 30, 2009

Thank you very much… very nice and successful presentation.
Orin Hooverson January 3, 2010

Thank you so much for publishing â€œEnable Remote Connection on SQL Server 2008 Express â€œ. It was exactly what I needed. Your solution was very clear and to the point. Next time I look to google for help and not just see how many time I can install the same issue.

Thank you for your time,
Orin
Arun January 8, 2010

Thanks…
NKO January 11, 2010

thanks! very helpful indeed
leo January 15, 2010

I can enable the sql browser
btw im seeing two instance. a sqlexpress and mssqlserver
Aaron January 18, 2010

thanks..
DJeffer January 27, 2010

Thanks a lot! Very helpful.
gelder February 2, 2010

Thanks my friend!
abhineet komal February 5, 2010

thanx, u have explained it brilliantly
Ulyana Arsov February 5, 2010

Thank you, this helps a lot, I love you. This only help me while days of researching for the stupid Login fails with that user. Thank you, thank you, thank you 🙂
charles February 6, 2010

Very Good Thanks 🙂
Gautam February 7, 2010

I have installed SQL server 2008 on windows server 2008 R2. windows authentication mode is working but SQL Server authentication mode is not working giving errors login failures error code 18456, I have not any SQL user I donâ€™t know the password of user â€œsaâ€ I created user in SQL Management studio, user created but could not login as sql server authentication. Please help
JVillamil February 7, 2010

Gautam:
You need a user in SQLServer to login and the pass off course
Gautam February 7, 2010

MR. JVillamil, I had already created a SQLServer user by using windows authenticated administrator login and this user couldn’t login. Furthermore I am using windows 2008 server R2 and I have also switched-off the firewall of private public network
JVillamil February 7, 2010

Gautam that’s rigth, I’m sorry I should say: change of
JVillamil February 7, 2010

Gautam that’s rigth, you should change of Windows Autentication to SQLServer Autentication to the user and the installation of SQLServer should indistinct in two cases…

I’m sorry by my english, but I only speak spanish (see you from Mexico)
Daniel February 7, 2010

This was really helpful but unfortunately I still can’t get a connection. I’m trying to use the “ASP.net SQL Server Setup Wizard”. Every time I try to get a list of the databases in SQL Server I get the remote connections error:

“Failed to query a list of database names from the SQL server. A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verifty that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 – Could not open a connection to SQL Server)”.
JVillamil February 8, 2010

Daniel: why no connect by TCP / IP ???
Discount Code February 8, 2010

Thanks a lot.. It was exactly what I was looking for. Much appreciated..
Gautam February 8, 2010

Hi Mr. JVillamil & all, The connection error of sql server authentication is solved by making a change in the registry.By default the windows server 2008 R2 having registry setting for the windows authentication by changing the value of registry key I could solve the problem.
linglom February 11, 2010

Hi, Gautam
Could you share what registry that you changed?

About your problem (error code 18456), you should look into the SQL Server’s log which is located in Program Files\Microsoft SQL Server\MSSQL.n\MSSQL\LOG\ERRORLOG and ERRORLOG.n files. You will see more details on the error message. The example error message should looks similar to this:

2006-02-27 00:02:00.34 Logon Error: 18456, Severity: 14, State: 8

You see that there is state number which tell the detail of the error message. Here are some common error states and their descriptions:
2, 5 -> Invalid userid
6 -> Attempt to use a Windows login name with SQL Authentication
7 -> Login disabled and password mismatch
8 -> Password mismatch
9 -> Invalid password
11,12 -> Valid login but server access failure
13 -> SQL Server service paused
18 -> Change password required

Reference: Understanding “login failed” (Error 18456) error messages in SQL Server 2005

Hi, Daniel
Check your SQL Server’s name to see if it is correctly. This wizard always generates the server name as [MachineName]. But if you are using SQL Server express, it should be [MachineName]\SQLEXPRESS.
dilip February 18, 2010

http://localhost:8080/reportserver but i want to use only http://localhost/reportserver but it not connect because port number require but how can connect without port number.
JVillamil February 19, 2010

mhmhmh, you should configurate the IIS to run the ReportServer to the port 80
Sinbad1 February 23, 2010

I can’t thank you enough !
Excellent walkthrough .
PBC February 25, 2010

Very good explanation. I am still in need of help. At Step 4, the following error message appears:

â€œThe service cannot be started, either because it is disabled or because it has no enabled devices associated with it.â€

Any advice?

Thanks …
terirog February 26, 2010

Thanks! Clear and very helpful.
Agus March 17, 2010

Hi, Mr. Linglom
I think this is very good posting,
but I have a question how about configuration setting ODBC for Ms. Access in PC Client to connect to the Windows Server 2008?, which is I put the master Database.
Tks for your answer…
GIJack March 17, 2010

I still cant connect using ipaddress
Andy March 19, 2010

Outstanding. Thank you for the straightforward (and correct!) explanation.

I’ve been going around in circles on this problem for hours, and Microsoft’s online resources have been unhelpful.
Gojko March 19, 2010

It’s awesome, thnaks….
kv April 7, 2010

thx for the info that was what i was looking for
Deepika April 14, 2010

I have completed till 9th step successfully..but microsoft sql server 2008 management studio does not found in my Start menu. Please solve this query.
linglom April 14, 2010

Hi, Deepika

You can use Microsoft SQL Server Management Studio from other pc to connect to the SQL Server. Or you can download and install the tool – Microsoft SQL Server Management Studio Express.
Mike April 20, 2010

Thank you for the article and thanks too to linglom. Having to append “\SQLEXPRESS” was not obvious at all. Changing my “Server name” value from “192.168.1.112” to “192.168.1.112\SQLEXPRESS” did the trick for me.
Ravi April 20, 2010

thanks a lot buddy,. it helped me a lot
Vibgy Joseph April 22, 2010

@PBC – Hey I also got the same error “The service cannot be started, either because it is disabled or because it has no enabled devices associated with it.”. I solved it by the following steps…

1. Start -> Run
2. Type “services.msc” and click “Ok”
3. Locate “SQL Server Browser” service
4. Right click on it and select “Properties”
5. In “General” tab select “Automatic” in “Startup type”
6. Goto “Logon” tab
7. Select “Local System account” in “Log on as” section
8. Click on “Ok”
9. Now right click on the service and select “Start”
wn April 26, 2010

Thanks, exactly what I needed.

Has anyone tested how many simaltaneous users can access the db installed like this for simultaneous w+r access? I know each db can have it’s own issues, but just wondering if there has been any testing / reporting?

Thanks
paramsrikant April 28, 2010

Thanks a lot. It is very helpful!
itl May 6, 2010

The “,” saved my day!!

So, if SQL is on different port then default 1433

SERVERNAME(OR IP)\InstanceNAME,PORT

ex: SQLSRV01\SQL2008,1588

thx!!
Keren May 17, 2010

THank you deeply!!! great article – helped a lot!
Edward May 17, 2010

I owe you one! This is sheer gold. Got it running v. quickly.
Andy May 19, 2010

Great article linglom!

For some reason my SQL Server Management Studio Express had to be given “Server1\SQLExpress,1433” to be able to connect to remote instance.
Kepa May 19, 2010

Thank you very much, is a perfect guide!!
RahulR May 20, 2010

Thanks a Lot! It worked for me!
Puroh May 20, 2010

Thanks.. This is how all guides should be written! Nice work
Jan May 31, 2010

Thank you so much! Been struggling with the port issue for hours.

So instead of a normal ‘: – then use ‘,’

Thanks!
Derrick June 3, 2010

thank you so much..

it help me a lot..

thanks..
Sarmad June 18, 2010

Thanks a lot man !! it is very Helpfull.
Sarath June 21, 2010

Nice article man. But my issue is a bit different. I have a server in which both SQL server 2005 and 2008 are installed. few databases are in sql server 2005 and few are in sql server 2008.
I am able to connect to 2005 from my local system but unable to connect to 2008 from local system. Can you please solve this issue.
Mehboob June 24, 2010

Yup,nice article but i had follow all the steps and still i can’t connect remotely to Sql Server 2008 database.
Sundar July 1, 2010

Great Thanks.
Robbie Reyes July 1, 2010

VERY NICE!! EXACTLY WHAT I NEED! THANKS!
Dave July 2, 2010

Thank you, Thank you, Thank you, Thank you, Thank you, Thank you…………….
Dams75 July 7, 2010

Thank you Mask for connection format.
You just saved me.
Cathy Ooi July 8, 2010

Thanks! This was very helpful. M$ should take note and give us concise simple instructions to their $#$@#$ idiosyncrasies.

Also much thanks to Bud Aaron for the link. This and Bud Aaron’s link answered simply what I can’t get out of M$ help.

Wish I’d seen this earlier. Would have saved me days of figuring out what the heck is wrong when I can’t connect remotely. And I can’t find any Listening PORT 1433 on “netstat -na”.
Indoril Nerervar July 11, 2010

“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: Named Pipes Provider, error: 40 – Could not open a connection to SQL Server)”

The above is the error I recieved and I have expended days on this incredibly annoying issue – I have trawled through forums and Google looking for answers, made changes to my Registry, contacted the rather stupidly named “Microsoft help”, uninstalled and re-installed SQL Server 2008 on numerous occasions and generally been ready to dump my Windows machine in the bin. I eventually arrived at this site, looked at the comments above and found that my SQL Service was not Listening on port 1433!

This is why people turn away from MS products. I am glad to see their market share dwindle slowly but surely and await the day when they will, end up where the sun doesn’t shine. I hope its sooner rather than later!

Rant over – btw – thanks to your post and the commentators.
Asifali Kannattil July 16, 2010

Hi Linglom,

Thank you for your valuable information, Iâ€™m able to connect to remote server now. But another problem still exists. I initialized a Publication and Subscription on Local serverâ€¦its working perfectly. But in the case of subscription for Remote server (Added Remote SQL Server Subscriber from New Subscription Wizard) nothing happens like error and also any updations. May I want to do something in Remote Server for â€œReplicationâ€?
Pls helpâ€¦
Asifali Kannattil July 17, 2010

Hi Linglom,

I have Two Machines (PC1 and PC2) and installed Sql Server 2005 on both. I initialized a â€˜Merge Publication â€˜ on PC1; Publication database was â€œdb_Inventoryâ€ .I ticked the check boxes â€œCreate a snapshot immediatelyâ€ andâ€ Schedule a snapshot Agent to run at the following timesâ€ and changed the schedule to occur every 5 minutes Daily.
I want to Subscribe this Publication on a database â€˜Masterâ€™ which is created on PC2. For that purpose I created a Subscription on PC1 (Subscriber and Subscription database is PC2 and Master. ) successfully.
I inserted new records to db_Inventory using My Accounting Software that is running on PC1. The problem Iâ€™m facing is that the newly inserted records to Publication database are not getting inserted in the Subscribing database Master. But I can insert records to this database by simply changing the Database connection in My Accounting Software (The two databases having same structure).

For testing purpose, I created a new database Master_B on PC1 and created a new subscription for that db. In this case, all records I inserted to db_Inventory are getting added in Master_B.

I donâ€™t know how to solve this issue. Help me plsâ€¦â€¦.
Manish Jaiswal July 22, 2010

This is a great great post…

Thanks
Jmunoz July 23, 2010

I’m setting Up a SQL Server 2008 R2 on a Windows 2008 Server, and I can’t get connected because of a Windows Firewall issue, any idea?
victor July 23, 2010

thanks from maputo!!!!
Pingback: Enable to create ODBC Connection to SQL Server 2008 Express « Network PC Tech Blog July 25, 2010
Rakesh July 27, 2010

Great .. was tensed .. thinking will I be able to do with SQL Server 2008 R2 Express or not .. your clear explaination solved my problem in minutes .. thanks
But remotly I was forced to use Server IP address. Can it be connected by using server name?

Thanks
Traci July 30, 2010

Thank you! Saved me a ton of time!
Farshad July 30, 2010

Hi , thanks for your kind help to all programmers like me.
I have to make a setup that has to be installed on many PCs in different (about 80)cities of my country , so I have to make a script to enable TCP/IP in SQL Express.
would you please help me if you have such a script to do this job ?
looking forward to hearing from you.
lots of thanks
Bruno July 30, 2010

Tks for this very helpful explanation!
But I still face a problem to find the server when I try the remote access of ODBC.
The message I get is
Connection Failure
SQLState: ‘01000’
SQL Server Error: 2
[Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionOpen(Connect())
Connection Failure
SQLState: ‘08001’
SQL Server Error: 17
[Microsoft][ODBC SQL Server Driver][DBNETLIB]SQL Server unavailable or access denied

Can you help me?

Thanks again
melih August 3, 2010

Thanks !!!!!
Jasper August 3, 2010

Thank you very much. Very clear explanation and works like a charm!
migro August 5, 2010

I got a named SQL server 2008R2 instance running on a 2008R2 Terminal Server. Mixed authentication is activated because my application connects with user and password via ODBC.

When I logged in with admin rights, the application can access the database without any problems. When I’m logged in as a normal user I can’t get access to the database. Do You got any suggestions to solve this problem?

When I establish a new ODBC connection to a SQL server/database running on a separate maschine it works with normal user rights. So I think the problem relies to the local instance of SQL Server 2008R2.

Thanks in advance for Your help.

Regards
michael
migro August 5, 2010

Problem is solved. The SQL Browser service wasn’t running 😉

Thanks a lot for this article.
pankaj August 9, 2010

Thanks a lot its really nice and helpfull
Thanks
Liberty!!! August 12, 2010

This also worked for my DEVELOPER Edition of SQL Server 2008.
THANK YOU!!!
THANK YOU!!!
THANK YOU!!!
Sandae August 12, 2010

THANK you!!!
sunsetquest August 13, 2010

Thank you!!!!!
fyi – I did not need to perform step one.
George August 18, 2010

Thanks, but I still have a problem. I can connect from machine/instance A to machine/instance B (‘green play-button’) with SSMS, but when I connect from machine/instance B to machine/instance A I see a ‘white dot’ instead of the ‘green play button’. Both firewall’s are off and I’m pretty sure the SQL Server Browsers are running and that I enabled TCP/IP on both machines…
Rajesh August 20, 2010

superb…thanks..u increase my challanging
Sivawut Srithammawut August 26, 2010

Thanks you so much.

This is very clear..

-Pipe
Ilya August 27, 2010

Thanks! It’s really great manual!
Paul August 29, 2010

Thanks for the guidance, solved my problem in minutes once I started reading through the comments.
Paul August 31, 2010

Thanks a lot…….
Jeffrey September 1, 2010

Thank you so much for this, I was working on this for a few hours trying to get it work. Real life saver.
eqirtas September 3, 2010

Thanks, yes it works but my one more question.

I want to attach mdf database file for database operations. As i can attach db file on my own local system and it works.

Is it possible if mdf file is on other systems. and then i can attach for operations??

I am using sql express with a desktop application in C#

Regards
Eqirtas
Chanty September 8, 2010

Thank-you, very much the tutorial is very clear and it worked!!!!
bpelhos September 9, 2010

Very important, just repeating one of the comments:

Donâ€™t forget to use connection format:
â€œSERVER_IP\SQLEXPRESSâ€
When asked server adress or creating ODBC connection
Jguzman September 11, 2010

thanks a lot man !!
david September 22, 2010

thanks for posting this, the step-by-step with screenshots were PERFECTO, just what i needed. thank you again for taking the time to publish this!
Imran September 22, 2010

It worked for me.
Thanks for posting this helpful material.
Abraham September 23, 2010

Worked for me as well. Thnx a lot of the instructions.
AD September 30, 2010

Great help!
transportinoradea.ro October 5, 2010

thanks a lot 😉
rodcompo October 6, 2010

I’ve tried this but still not getting connected. I have a SQL Express R2 instance on my XP desktop I’ve got a laptop with W7 also running a SQLExpR2 instance. I want to connect the laptop to the desktop instance (and vice versa) using the SQL Management Studio but getting no joy. Got the browser service and network protocols running but still can’t see or connect to either instance from laptop or desktop – any ideas please
Pingback: multi seat set up... - MediaPortal Forum October 9, 2010
linglom October 11, 2010

Hi, rodcompo
It could be blocked by Windows firewall, try to disable Windows firewall and reconnect again.
kushi October 17, 2010

Awesomw steps and comments !…. it worked like a magic after 1 week of struggle to connect to my sql server db.

thaks a ton 🙂
Abhay October 18, 2010

Superb work, This will help me a lot.

Regards
Abhay
phpbugs October 22, 2010

I am able to connect from remote machine using the “sa” username. I created another user called “asd” and I am not able to conect to the DB with this user.
Please help!!!!!!!!!!!!!
Leonardo Suarez October 26, 2010

Hi, Is needed the Sql Server Browse is started, I think its dangerous if you put the DB server in internet. There is another way or I’m just in panic. Thanks
phpbugs October 26, 2010

I got it worked. I deleted the user “asd” and created the user with sysadmin privilege.
CCSWebWeaver October 27, 2010

I followed your instructions and now I can connect my SQL Express 2005 and SQL Express 2008 servers to / from MS Access 2003 database; this opens up a whole world of possibilities for me!

Thank You for this wonderful well written guide!
tobon424 October 27, 2010

I have 5 computers connecting to a DB
3 connecting fine, but 2 are not connecting, all computers are winXP Pro SP3

This is the error message that I get from the two computers I cannot connect:

Error Number: -2147467259

SQL 2008 could not open a connection to SQL server [53]

I know something is blocking the connection, but not sure what it is.

Why the other computers can connect, all are connected to the same router, switch etc.

I can see all computer within network, I am able to ping the server from the computer I cannot connected to the DB.

Thank you
Manuel Tenorio October 28, 2010

Thank you a lot for your excellent post!

Some of us just don’t born with the DataBase Management gift… whatever that means 🙂
balakrishnan October 28, 2010

Thanks friends, please tell us some more tutorials, Thank you
Geo November 3, 2010

I’m having the same problems I cant connect to the db from any other server. I have checked that SQL browser is running but I cant see it in config manager but I can see it under services? any thoughts SQL 2008 windows sever 2008.
Thanks in advance for any pointers
Bungei November 4, 2010

Big Thanks for this, my issue was fixed in seconds!
phoeniks November 10, 2010

THANKS for this! You are great!
Elham November 10, 2010

hello and thanks
but I did all tasks that you said in your posts but it also doesnot work and return Error26.
My windows is vista.
what can I do now…??
sim November 10, 2010

Thanks alot! That was really useful.
kachwahed November 11, 2010

Hi, Great thanks for share
I did all tasks and I still get:
[Microsoft][ODBC SQL Server Driver][DBNETLIB]SQL Server unavailable or access denied
Bob_SA November 11, 2010

Thanks linglom! Ive used quite a bit of info from your blog over the past few days getting sql2005 express and sql2008 express running concurrently(dont ask) with remote access. Saved me a lot of time!
linglom November 12, 2010

Hi, Elham
This post could help about error 26 – SQL Network Interfaces, error: 26 – Error Locating Server/Instance Specified

Hi, Kachwahed
It seems that you are using ODBC driver. Can you show your connection string?
Greg November 14, 2010

This worked. Thank you!
Why they disabled TCP, Is it any danger to enable it?
Kachwahed November 16, 2010

Hi, linglom. Thanks for reply
It’s OK now, I think the issue was in “Listen All” in TCP/IP properties, it should be set to “Yes”, add that in the article.
Great thanks linglom
Regards.
ahmet November 21, 2010

i did everything but it didnt solve my problem
btaz125 November 26, 2010

Hi,
How to config SQL Server 2008 to connect over Internet.
victor November 30, 2010

thank you very much ..
Jeff November 30, 2010

Hi,

I followed the entire guide. I disable windows firewall, enable DMZ mode for my computer with mssql server.
I am able to connect from a differnt PC under the same LAN. However when I try to connect from a PC under a different network using SQL Server Management Studio and it doesn’t work and gave me error 26. I followed one of your links on solving error 26 you provided and followed the content listed below:

) Make sure your server name is correct, e.g., no typo on the name.
2) Make sure your instance name is correct and there is actually such an instance on your target machine. [Update: Some application converts \\ to \. If you are not sure about your application, please try both Server\Instance and Server\\Instance in your connection string]
3) Make sure the server machine is reachable, e.g, DNS can be resolve correctly, you are able to ping the server (not always true).
4) Make sure SQL Browser service is running on the server.
5) If firewall is enabled on the server, you need to put sqlbrowser.exe and/or UDP port 1434 into exception.

I am pretty sure I meet the requirements for all 5 points above. For part 3, I am able to ping the server in command prompt, is that good enough?

Anyways I am totally clueless as what is wrong in my configuration. I would very much appriciate it if you can give me some pointers.
Thanks!
Pingback: CodeEase.com » Blog Archive » Again: Remote connect to SQL Server 2008 Express December 1, 2010
Robertas December 1, 2010

That worked, thanks!
Arinola December 2, 2010

oh thank you thank you soooooooooooomuch. WORKS greatly for me. i am so happy..thanks!!!
Eric'H December 2, 2010

Thank you! Thank you very much!!! : )
kyriakos December 2, 2010

Thank you!!!!!
Jeff December 3, 2010

Hi,

I followed the entire guide. I disable windows firewall, enable DMZ mode for my computer with mssql server.
I am able to connect from a differnt PC under the same LAN. However when I try to connect from a PC under a different network using SQL Server Management Studio and it doesnâ€™t work and gave me error 26. I followed one of your links on solving error 26 you provided and followed the content listed below:

) Make sure your server name is correct, e.g., no typo on the name.
2) Make sure your instance name is correct and there is actually such an instance on your target machine. [Update: Some application converts \\ to \. If you are not sure about your application, please try both Server\Instance and Server\\Instance in your connection string]
3) Make sure the server machine is reachable, e.g, DNS can be resolve correctly, you are able to ping the server (not always true).
4) Make sure SQL Browser service is running on the server.
5) If firewall is enabled on the server, you need to put sqlbrowser.exe and/or UDP port 1434 into exception.

I am pretty sure I meet the requirements for all 5 points above. For part 3, I am able to ping the server in command prompt, is that good enough?

Anyways I am totally clueless as what is wrong in my configuration. I would very much appriciate it if you can give me some pointers.
Thanks!
Daniel December 3, 2010

Great work!
Thanks
rick December 4, 2010

tks 4 details!
Arjun December 5, 2010

It was very helpful. Thanks a lot.
mrcity December 5, 2010

One thing that hung me up for a while was the different “modes” of Windows Firewall — domain, private, and public. In testing, I had only shut off Domain, where I should have disabled the other two. Of course, the best thing to do is to add an exception for the particular port you’re using across all 3 modes.

@Jeff, perhaps try looking at your TCP/IP configuration in Sql Server Configuration Manager (as described above), but double-click on it to examine the settings. In the “IP Addresses” tab, make sure the correct IP addresses and ports are enabled and set (you probably want to clear “Dynamic Ports” and enter a static port # in “TCP Port” – most people pick 1433). Go to the bottom, to IPAll, clear “Dynamic Ports”, and put in the desired port # in “TCP Port” just like the ones you have enabled above.

– Stephen
Jeff December 7, 2010

@Stephen

The TCP/IP config are all set just like you stated, yet no luck.
Mayur December 8, 2010

Thanks a lot … keep up the good work.
SRAVS December 21, 2010

great job!!!!!!!!!!!!!! resolved my issue……..THANKS 🙂
nildeen December 27, 2010

It would be great to expand this great walk-through to include server 2008 firewall setup 🙂 I’ve turned off the domain inbound for now and will need to go figure out the rest later.

Anyhow, thank for the help!
Ivan Lewis December 28, 2010

Can i connect to sqlserver 2008 express from sql server 2005 express edition?
MedMost January 4, 2011

Thank you very much

it help to solve my problem

i think i cant log on server2008 from server2005express
Terry January 13, 2011

Really great..!!!

I have followed it without any error or mistake.

Very good.
Guido January 13, 2011

Bravo!!
Roman January 18, 2011

If you follow the above steps, but the remote machine cannot connect, check your firewall software to ensure ports 1433, 1434 are open.
Teimoor Khan January 23, 2011

Thanks A lot to publish this article…….this article would help me in my fyp…….
Chuck January 25, 2011

Thank you. This helped me today.
Plal January 27, 2011

As mentioned in 89. “,” worked for me. Thanks!
Ankit February 3, 2011

hi.. Can we achieve this using script or command prompt??
jcy February 17, 2011

excellent guide, thank you
Domino February 17, 2011

My great thanks!
Adam February 18, 2011

When all else failed your instructions worked.

FYI – I did have to reboot after following instructions.

Thanks again.
lax February 18, 2011

Hi thanks in advance.
There are three machines in LAN.
Mac 1 contains windows server 2008 + Sql server 2005 + package X accessing local db
Mac 2 contains windows 7 + Sql server 2005 + Package Y accessing local db
Mac 3 Contains windows xp

problem
from mac 2 and 3 package x is working and retrieving data from x db
mac 3 access package x and its data, but not y and its data.
janerose February 19, 2011

O M G you are an angle sent down from heaven i want to buy an aeroplane and a jet ski. I have been trying to fix this error for the last 7 days my eyes hurt i was on the verge of jumping from my staircase fml. My stupid teacher who has been teaching this university module using this software for the last flipping 10 years he had no clue and gave no help (he will be egged soon).

anywayyssss thankkkk youuu so much!!
Steve February 22, 2011

Excellent information. Thanks. You save me hours of work.
Mindwarp February 23, 2011

Excellent! Thank you very much for this information!
Ivanov February 23, 2011

Thanks indeed!
I did what you told, but had to do a bit more. It was about the ports in the Firewall.
In the “server” I opened for incoming these ports:
TCP 1433
UDP 1434
And also authorize the sqlserver.exe through the Firewall.
More on that, here:
http://msdn.microsoft.com/en-us/library/cc646023.aspx
MathewUy March 3, 2011

Thank You so much for this very useful information,
GOD Bless people like you who take time and effort in making this.
Darshan March 10, 2011

The tech blog was really useful it was very informative. Thanks a lot!
Krzysztof March 10, 2011

Big thanks. This tutorial is verry helpful, working good 🙂 Greetings from Poland
Jared March 19, 2011

Great post.

I had the same error as Moonheart in Nov 2009: “Cannot connect to WMI provider.” For anyone else with the error, I solved it with the help of this post: http://msmvps.com/blogs/martinpoon/archive/2009/11/27/sql-server-configuration-manager-cannot-connect-to-wmi-provider-invalid-class-0x80041010.aspx.
Nirav March 22, 2011

Thanks a lot mate…really helped..
Suresh March 22, 2011

Hi, I have installed sql server 2008 on a windows server 2008 system.I would like to access sql server browser to query data from other system but this doesn’t sql server 2008. Is it possible to access like that ? I followed your steps. Thanks.
linkoblast March 30, 2011

Excellent….I was looking for such a good solution…..it saved my day!!
Roshani March 31, 2011

Thanks a lot!!!!!!!!!
You solved my big problem..I was able to connect to the SQL server on local but was not able to connect from remote machine…

I changed the server name to IP\SQLEXPRESS

Thank you
Wahaab April 4, 2011

You were a great help!!
there are rare articles which address a problem step by step!!
Dude April 5, 2011

You’re super!!this article helps me out from problem connecting to SQL Express 2008. Great thanks…
Joy April 13, 2011

Great Article Superb Thanks to you. i was struggling quite a bit in connecting to the sql server . you saved my day 🙂
Krumov April 19, 2011

Thank You !!!
This is something that must be given all kudos and admirations. GREAT JOB !!!
gleija April 20, 2011

🙂 thanks a lot!!
Suzy May 9, 2011

Thanks you so much – your document helped me to finally get my remote access working. I had to blank out the dynamic port settings in the TCP/IP settings (in IPall), and add 1433 as specific port.
Fourier May 18, 2011

Congratulations, the secret is the service SQL Server Browser. Thank you very much for your article.
Cobesauce May 19, 2011

Awesome!!! 3 days, whole IT dept. could not solve it. But, I solved it from this once I found you guys!!!
taner May 23, 2011

Great one. Thanks
Ramesh May 27, 2011

Thanks for ur guidance for enable the remote server in SQL Server 2008, Its very helpful for me and I have connected as per ur instruction. Thanks for posting this article.
Marie June 9, 2011

Thank you so much for this detailed post. Perfect!
Nimda June 9, 2011

Thank you. Very helpful
Nripin June 15, 2011

Thanks for the tip mate !!
Keep up the great Work !!
Cheers
Nripin
Moeez June 16, 2011

It is not worked for me. I have as it was written but can not access my instance remotely.
AlexF1980 July 26, 2011

I tried everything listed above, and none of it worked. Then I checked another instance I had running (that I recently upgraded to SQL Server 2008). It turns out that (having setup the firewall correctly first!) changing the “TCP Dynamic Ports” under the IPAll section of the TCP/IP Properties dialogue to 1433 (and a restart of the SQL Server service) allows the remote connection to take place.
Marco July 31, 2011

Thanks for the help!
This may help too:
(1)
Look at the error log:
Server Management Studio -> Object Explorer -> under you server connection expand Management, expand SQL Server Logs -> Open up Current
(2)
Find osql.exe and run this at the command line:
osql -E -S %SERVER%\%INSTANCE% -Q “sp_grantlogin ‘NT AUTHORITY\NETWORK SERVICE'”
devesh August 1, 2011

nice problem solve trick. thanks ………
ravell August 4, 2011

It did not work for me. I used the link below and it is work fine. Besided the diffrence is changing TCP port to 1433 and removing Dynamic port information.
http://social.msdn.microsoft.com/Forums/en/sqlexpress/thread/97a0ddf7-d23e-4723-b49f-463943180172
Ken August 16, 2011

Thank you for posting these steps. They helped more than you know.
Sandeep August 17, 2011

Very usefull post…Thanks dude
abhi August 23, 2011

i have window 7 64 bit. Can 32 bit machine will be able to view my reports or i need some special settings as nthg working for me now……rep asap
abhi August 23, 2011

i am not able to start my sqlexpress too? wats d problem in dat????? some error is dere like…..application not responding in timely fashion….???????? i installed sql 2008 properly
Emil G August 27, 2011

When connecting to Microsoft SQL Server and trying to use usually SQL Authentication method you may get error 18456 login failed for user you provided. This usually means that your connection request was successfully received by the server name you specified but the server is not able to grant you access for a number of reasons and throws error: 18456. It sometimes provides also state 1 which actually isnâ€™t that useful as due to security reasons any error is converted to state 1 unless you can check logging on the server. Check here for more info microsoft sql server error 18456
sqlserver August 28, 2011

You can also check the SQL Authentication online using the quick free tool at:
http://www.webkeet.com/sqlservertestconnector.aspx
JimiSweden August 31, 2011

Thanks for a great guide!

//Jimi
http://newsweb.se
DKronen August 31, 2011

Exactly what I needed

Thanks
Izaks September 12, 2011

Good one. solved me a huge problem.

Thanks.
jim September 14, 2011

thx ig4 (post #22)

“check win server 2008 firewall public profile state =off
startâ€“>Administrative Toolsâ€“>Windows Firewall with Advanced Security, to click Properties,to select Tab â€œPublic Profileâ€ set firewall state =off.
Donâ€™t need restart machine.”

that fixed it for me. thanks!
Tushar September 23, 2011

Great article it rocked.
Thanks so much.
Al September 23, 2011

I’ve followed every step…no problems and then I get step 10. It says ‘connect to SQL server 2008 express’ but I still can’t do this!

Get the following error message….It’s driving me mad….any advice gratefully received!!!

TITLE: Connect to Server
——————————

Cannot connect to (local).

——————————
ADDITIONAL INFORMATION:

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: Named Pipes Provider, error: 40 – Could not open a connection to SQL Server) (Microsoft SQL Server, Error: 2)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=2&LinkId=20476

——————————
BUTTONS:

OK
——————————
Al September 23, 2011

YESSSSSS – it was the IPAall 1433 problem!!! ok now…phew
Peter Olofsson September 27, 2011

Excellent!
Thanks
Kamlesh October 6, 2011

Thank you very much…
It’s really helpful
Paul October 10, 2011

Awesome! Thanks a lot!
Soheb October 11, 2011

Thaaaaaaaaaaaaaaaaaaaaaaaaanxxxxxxxxxx….
SeriousM October 12, 2011

GREAT!! Thank you sooo much :)!!
Khalane Rapuleng October 13, 2011

Great work: It really come for my rescue
Brad October 18, 2011

Great tips….I seem to recall the protocols needing to be enabled but not the browser service
Aws October 19, 2011

that is great. thank you alot 🙂
Neeraj October 21, 2011

it’s really very helpful.Thank you..
Farhan November 3, 2011

I added 1433 to ipall even after that its not working, I have done all steps, 1433 added to firewall sql server browser service is running also..
Raja November 16, 2011

Thanks for valuable Informations
Sebastien November 17, 2011

Thanks for the info. It worked!!
Roberto November 18, 2011

work for me, thanks!
Chris J November 22, 2011

Thank you so much… 2:18 am and you saved me!
Farhan November 22, 2011

I am working on a dedicated server I guess some upper level security is not letting me to open port 1433 so I have to install a software TunnelEX to open port 1433 and after installing this software I am able to connect remotely to SQL SERVER 2008 Express Finally
Christine November 22, 2011

Hiya!

This has been incredibly useful, however, I’m still falling down at the last hurdle of getting an ODBC connection from a machine that is also running SQL Server 2008 Express. I can register the remote server via SQL Server Management Studio, I’ve changed the port to 1433 in the TCP/IP configuration and then when that didn’t work to 14330 as per someone else’s suggestion. However, the error I’m getting with my ODBC connection is:

Connection failed:
SQLState: ‘010000’
SQL Server Error: 11004
[Microsoft][ODBC SQL Server Driver][TCP/IP Sockets]ConnectionOpen (Connect()).
Connection failed:
SQLState: ‘08001’
SQL Server Error: 6
[Microsoft][ODBC SQL Server Driver][TCP/IP Sockets]Specified SQL server not found.

So the configuration is:

Machine 1: Windows XP, SQL Server 2008 Express. Remote connectivity enabled.
Machine 2: Windows XP, SQL Server 2008 Express. Can add Machine 1 to SQL Server Management studio but not set up an ODBC connection. Can connect via ODBC to machine 3
Machine 3: Windows 7, SQL Server 2008 Standard. Can add Machine 1 to SQL Server Management studio and connect via ODBC to Machine 1

What could it be on Machine 2 that will stop me connecting to one server via ODBC but not another?
Christine November 22, 2011

Ignore me! I was being an idiot! It’s now working absolutely fine – in the SQL server name I’d used a forward slash instead of a backslash!

In the immortal words of Homer Simpson – d’oh!!
Elnora Kailey November 24, 2011

Fantastic web site. Lots of helpful info here. I am sending it to a few buddies ans also sharing in delicious. And of course, thanks for your sweat!
Dannette Sarcone November 25, 2011

I’m not positive where you’re getting your information, but great topic. I needs to spend a while studying more or working out more. Thank you for wonderful information I was in search of this info for my mission.
Alberto Juliao December 3, 2011

the comment #89 helped me with the issue starting SQL Browser, I’m new to creating remote connection with databases, However I want to know what specific IP must be set on the connection String, does it need to be the public ip of my local machine? Or the one I assign from the TCP/IP? Please answer as soon as possible.. THANK YOU FOR THIS GUIDE.
Josip December 4, 2011

Hey man, thank you for the detailed informations and step-by-step leading through the problem! It helped me so much that I can’t tell you how happy I am 😀
YK December 8, 2011

Thank you! It helped me! 🙂
Alberto Juliao December 9, 2011

Ok I figured it out, you need to type down your public IP address if you’re connecting from outer network (i.e friend’s home) if you’re connecting from the Server PC then, type your internal IP (192.168..) That’s all. And also what this post is missing is the steps of allowing windows firewall to sqlserver and its ports. Also the creation of accounts and users login that you choose to access the database you want to allow remote connection. Tutorial in this link: http://videotutors.net/sqlserver-remote-access.aspx
Mustapha December 10, 2011

Hi all,
I have followed steps , until step 10 , when I open Microsoft visual studio management . when I click on connect to , I see only database engine and SQL Server Compact ,but not he SQL Server 2008 Express .
I d appreciate if someone can help me with this issue.
Regards
Alberto Juliao December 10, 2011

@Mustapha, there’s only those two features (Database Engine and SQL Server Compact) what he means for “SQL Server 2008 express” is connecting to the core service, which is “Database Engine”, it’s selected by default you just need to type your instance name in the “Server Name”, also “NameInstance”/SQLEXPRESS can work, by default the instance name is your Computer Name.
Henry Lafferty December 13, 2011

Brilliant mate – worked first time :0)
Marcelo Castilho December 24, 2011

Very good!!! ItÂ´s all rigth.
Kazinski January 2, 2012

I have followed the steps above a couple of times and I am still having no luck in getting the server to talk from one machine to another.

I can log on the local machine using Server Management Studio but I can not from a second machine on a local network.

When I use sqlcmd – > sqlcmd -S \MSSQLSERVER it does not work, but if I use sqlcmd -S I get a sql command prompt.

I am getting a Connection string is not valid [87] error.

Any help would be greatly appreciated.
Kazinski January 2, 2012

I am now able to connect. I use the IP address of the server and I am able to connect using a different account I set up. I was trying to log in using my admin account for the sql server and that is failing.

I think I have 2 problems – the first is a domain name problem. The computers are connected in my home network via workgroup and the 2nd issue is account permissions.
Asim Raza January 9, 2012

This is best.
abdullah January 16, 2012

thank you dear, i appreciate that!
Chris January 17, 2012

Thanks mate!
nwaomachux January 18, 2012

I’d like to ask you, since I do not have Microsoft SQL Server 2008 Management Studio Express installed on the PC that’s running the SQL Server 2008 R2, how do I connect to it from another PC.

I installed the SQL Server on a Virtual Machine. I’m able to connect to the VM, but the problem is that I followed the above tutorial till I got to the point where I’d have to work further with Microsoft SQL Server 2008 Management Studio Express but I’m not able to finish up because I don’t have it installed.

I will like to use another program to connect to the server. Please, how do I do it. Thanks.
Farhan January 18, 2012

install a port redirector like TunnelX , It is going to work.
Raghava January 20, 2012

THANKQ VERY MUCH……………….
Stan January 22, 2012

Great write-up. Excellent.
ilias January 22, 2012

Thanks!!
Rick February 5, 2012

Re: SQL Surface Area configuration.

SQL 2008 Express does have Surface Area configuration. It is now (hidden) in “Facets” (see context menu), last option of the list. Ad hoc remote queries can be enabled there.
Wellington February 8, 2012

Thanks man !! You have helped so far in Brazil.
Haza February 12, 2012

Gracias!!!!!!!!!!!!!!!!!

Muy bueno =) y muy util

100% funcional
Roopesh February 14, 2012

Perfect. Somehow i missed to enable tcp/ip and this article helped and saved a lot of time. Nice article.
ANkit Roy February 24, 2012

Thank you It worked for me…
Nicola Matarese March 5, 2012

Very useful tutorial! It worked fine for me.

Now I have a question:
is possible to establish a remote connection on SQL SERVER through a VPN using the Windows authentication?

Thanks!
dba March 8, 2012

thanks alot dude .. it is help me much for my project……
Wombat69 March 9, 2012

This was great, I am migrating a database to another server & wondered why I could not connect from a remote pc, now i have followed your detailed instructions & now it is working.

Thank you, saved mae a lot of hours
cape March 19, 2012

Thank You very much.. its relly relly really help me!
Poetry April 6, 2012

Great weblog right here! Also your website a lot up very fast! What host are you using? Can I am getting your affiliate link to your host? I want my site loaded up as fast as yours lol
Halmeic April 7, 2012

Trying to do the same thing on windows 7 and MS SQL 2008. The menu tree is different can anyone help?
Filme April 9, 2012

Thank you a bunch for sharing this with all of us you actually understand what you’re talking about! Bookmarked. Please additionally visit my web site =). We can have a link alternate contract between us
Tihanyi ZoltÃ¡n April 13, 2012

Hi Man !
Millon thank’s for you! You saved my life 🙂
Nio April 17, 2012

Hi,
billion thanks for your help.!

You really saved me.
Mickey April 17, 2012

Nice and clear post. Thanks.
Inno April 17, 2012

I am using a university computer connected to the university network. I don’t have admin rights. I did install SQL Server 2008 myself. SQl Server is running by default but SQL Server Agent and SQL Server Browser are both disabled so I could not start them by following the above instructions. Any way out? Please.
hicsel April 17, 2012

Windows 7 won’t let you install most programs without the correct authorisation, so I take it your using an older OS. Any form of Server setup and configuration usually requires Admin rights. I think your very lucky it let you install SQL Server in the first place. Speak to the IT manager and ask him to elevate you to the required level of access.
If you still have problems come back on.
Ernesto April 18, 2012

Eres el mejor.
Gracias.
Ranjit April 18, 2012

Thats a very useful blog dude. Thank You very much
Yamini April 19, 2012

i did all settings which you mentioned above but still have bellowed error for remotely connection to SQLEXPRESS server.
Error: 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: Named Pipes Provider, error: 40 â€“ Could not open a connection to SQL Server) (Microsoft SQL Server, Error: 53)

Can anybody giude me please…!
Kurd April 20, 2012

Greate guide, it helpt me aswell 🙂
linglom April 25, 2012

Hi, Yamini

Here is a checklist that you could do:
– Check if the SQL Server service is running.
– Check if there is any firewall blocking the connection between the SQL Server and the remote computer or not.
– If you are connecting by programming, you should verify the connection string if it corrects or not.

If you have trouble in any step, let me know.
Mubarak April 29, 2012

Hi guys,

I’m VS 2008 to upload excel sheet in remote machine. but local machine, it was working fine. and its through the error like below, while I’m uploading excel sheets in applications. please go through below,

please help me to resolve the issue, I’m in critical situtaion. any help will be greatly appreciated. i did all cahnges in remote machine even I’m installing the SQl 2005 in remote server and enable the port 1433 in firewall, and enable the database engine to local & remote connections. but still the below errors are appear.

please help me to solve the issue, its my humble request.

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: Named Pipes Provider, error: 40 – Could not open a connection to SQL Server)
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.SqlClient.SqlException: 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: Named Pipes Provider, error: 40 – Could not open a connection to SQL Server)

Source Error:

An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.

Stack Trace:

[SqlException (0x80131904): 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: Named Pipes Provider, error: 40 – Could not open a connection to SQL Server)]
System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +4849719
System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +194
System.Data.SqlClient.TdsParser.Connect(ServerInfo serverInfo, SqlInternalConnectionTds connHandler, Boolean ignoreSniOpenTimeout, Int64 timerExpire, Boolean encrypt, Boolean trustServerCert, Boolean integratedSecurity, SqlConnection owningObject) +4863021
System.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo serverInfo, String newPassword, Boolean ignoreSniOpenTimeout, Int64 timerExpire, SqlConnection owningObject) +90
System.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(String host, String newPassword, Boolean redirectedUserInstance, SqlConnection owningObject, SqlConnectionString connectionOptions, Int64 timerStart) +342
System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance) +221
System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance) +189
System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection) +185
System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnection owningConnection, DbConnectionPool pool, DbConnectionOptions options) +31
System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject) +433
System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject) +66
System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject) +499
System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection) +65
System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory) +117
System.Data.SqlClient.SqlConnection.Open() +122
Zakat.PhoneBook.Contacts.XLUpload(DataTable dt) in D:\Rahman\Clients\Zakat\Zakat\Zakat\PhoneBook\Contacts.aspx.cs:469
Zakat.PhoneBook.Contacts.UploadXL() in D:\Rahman\Clients\Zakat\Zakat\Zakat\PhoneBook\Contacts.aspx.cs:244
Zakat.PhoneBook.Contacts.Button1_Click(Object sender, EventArgs e) in D:\Rahman\Clients\Zakat\Zakat\Zakat\PhoneBook\Contacts.aspx.cs:452
System.Web.UI.WebControls.Button.OnClick(EventArgs e) +111
System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) +110
System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +10
System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +13
System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +36
System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +1565

Wating for your kind reply guys,

Regards,
Mubarak
Mubarak April 29, 2012

Dear linglom,

I’m checking that SQL server is running, and the connection string also working fine in all other modules. but especially the when i import excel sheets in my application, the instance error throws..

1. can you please giude me more on this and please giude how can i check that, Is ther any firewall blocking the connection between the SQL Server and the remote machine..??

2. I’m also checking the instance with conenction string like data source=”zak_db01\zakat,1433…etc”. –> but there is no use of it.

please help me to reolve this issue.

Anyhelp will be greatly appreciated. I’m in critical situation.

Thanks
Mubarak
Sethu May 1, 2012

Great. It was worked fine. SQL Express 2008 R2 – Remote connection issue has been resolved by followed the steps given. Thanks a lot. 🙂
Ola May 2, 2012

A Millions thanks from Norway!!!
Pingback: Unable to connect to SQL Server Express from a remote machine. « NetworkPCTech Blog May 8, 2012
Mike May 11, 2012

thanks! it’s very effective…. ^_^
Stef May 23, 2012

hi,
did everything and still not working… suggestion anyone??

they changed the server and before it was working and now…
thank you
Stef
Zahir June 12, 2012

Thank you very much! This tutorial was very effective, everything works in just couple of minutes.
Thank you,
Zahir
Amir Yaqoob June 13, 2012

Different version of SQL server may cause to avoid remote connection:
I struggled around 4 hours, and it did not work. Finally, I realized that I have some installtions of SqlServer 2005 in addition to Sql Server 2008 Express edition. I gave last try by removing Sql 2005 installtions, and then try to connect with SQL Server 2008 Express instance. and got success.

Thanks!
Daksh Bhatt July 1, 2012

thanks buddy . . .
i solve my problem in 5 minutes . . .
KW July 6, 2012

What “nonstandard” happened to me when I was configuring remote MSSQL (maybe someone said it above):
– my SQL Server process didn’t open on standard 1433 port, but some 50000 (check it in netstat)
– On client I used Server’s port, not Server Browser’s
– Windows Firewall by default blocks Server’s port! I disabled it totally until I made it work.
– pay attention to User name and domain you are using – I used same (domain) user remotely, as I installed server on, and it worked

First make it work at all costs. Then play with setting ports, firewall and so on.
Osmumos July 19, 2012

This is awsome!!!
I has sorted me big time!
Thanks and Best Regards.
ivykoii August 20, 2012

thanks! great tutorial for that error.
Mostafa August 21, 2012

Thanks!

hi
thanks a lot for training
Christos August 26, 2012

Very nice!!!! Thanks a lot!!!!
Israeli Dude August 28, 2012

Great Manual! Thank you!!!! 🙂
oderu September 4, 2012

Very helpful! Thank you very much!!!!
Pingback: When you cannot connect remotely to an MsSQL server …. - Data Management Center October 2, 2012
Dave October 9, 2012

I have a Sql express server at my h.office.Im running a application from my brance.i have a static ip at my h.offc.
how i can connect my appl to my h.office sql express?
Bill October 13, 2012

Thanks! Your article just saved my weekend. Had a client on SQLServer Express, who now wanted to access it on a remote networked computer. When it did not initially connect, I figured I’d be at it all weekend trying to find the solution. It is 8:00A.M. Saturday morning and I’m on my way to the golf course. Thanks a million!!
Dave October 17, 2012

Thks Bill.
Any way i have sort out the issue over the weekend
Murphiks October 26, 2012

A very good statement.
All but one of the super, nobody faced with connection from Linux hosting via MSSQL or ODBC , port 1433 to Windows server 2008 R2 computer with SQL Express 2008 R2 ?
All set up correctly with the other computer on Windows server 2003 I’m conecting via XAMPP 1.7.1 and MSSQL.
host=192.x.x.x,1433
I no it does not work. Someone tell me or anyone advise? As early as the days of steam no results 🙁
On linux Hosting is on FreeTDS ver.8.
Ãlvaro October 29, 2012

Great manual!!! Thanks!!!
Steve November 20, 2012

Many thanks for this post. Broke a log jam for me.
Mariola November 23, 2012

Very helpfill indeed. Thanks a lot.
rvfigueroa November 25, 2012

Just one think, and elemental!!!!

You need to verify your Windows Firewall in your machine that run your SQLEXPRESS!!!

You can add and rule to your firewall or directly deactivate for a few momento while you work with connection.

Regards!
shabnam December 19, 2012

I have 2 sql server in win server 2003. sql server 2000 and sql server 2008 R2 .I want to remote connection to both of them from the client system. but I could remote to sql serevr 2000 but I could not remote to sql 2008 and show this message:
Cannot connect to SQL-Server-Instance-Name
An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 26 â€“ Error Locating Server/Instance Specified) (Microsoft SQL Server)â€

what I am doing?? please help me… thanks alot
Alberto Juliao December 19, 2012

@shabnam,
1. did you follow every step for this tutorial?
2. If not, for “SQL Server 2008 Express” you go open Microsoft Visual Studio Management, there will be two features (Database Engine and SQL Server Compact)
3. To manage â€œSQL Server 2008 expressâ€, go to â€œDatabase Engineâ€, itâ€™s selected by default you just need to type your instance name in the â€œServer Nameâ€, also â€œNameInstanceâ€/SQLEXPRESS can do the trick as people said, by default the instance name is your Computer Name.
4. If SQL is on different port than default 1433
SERVERNAME(OR IP)\InstanceNAME,PORT
example: SQLSRV01\SQL2008,1588
5. If still no luck, please read this post that includes Firewall configuration and TCP/IP config http://videotutors.net/sqlserver-remote-access.aspx
Merry Christmas.
masud January 22, 2013

Has XP2 but win 7 cann’t login

Help
Ponnusamy January 23, 2013

Windows 7 OS, I have tried from java to connect MS SQL 2008 server. I am having 2005 also in my machine. Here I was not able to connect 2008 server since 1433 port was assigned to 2005. So I have changed the port in my application to port which is mentioned in SQL Server Configuration Manager ->SQL Server Network Configuration ->Protocols-> TCP/IP properties for All IPs
fateme January 25, 2013

every body teach me a thing is my GOD. thanx alot dear, it was the thing that i realy needed.
Krajisejck February 7, 2013

Tnks a lot…
=)
Ahsan Ashfaq April 25, 2013

Thanks for such helping post . It really help me . Thanks again .
Sarang May 1, 2013

Thank you so much!!!
andriy May 16, 2013

Thanks a lot!
Costin August 25, 2013

Thank you for information, was very clearly and helpfull!
bogser September 13, 2013

Thanks for this very helpful information.
After reading this article, it just took me some more minutes to get the external SQL Server connection working !
Much appreciated.
Benjin October 31, 2013

Cheers mate, now sorted.
sajjad December 30, 2013

Thank you very much.
Tomas Sancio January 28, 2014

Thank you.
Thank you.
Thank you.
khalid z? March 4, 2014

thank you very very much, so helpful tuto.
Pingback: [RESOLVED]Enabling SQL Server 2008 (R2) access over Network | ASP Questions & Answers July 3, 2014
GB January 27, 2016

Thanks for the blog + suggestions from everyone, which helped me to get connected to ec2 – sql server studio from my local machine.
Wagner July 15, 2016

Muito obrigado, me ajudou muito, vocÃª nÃ£o imagina o quanto. AbraÃ§o
Barbar January 30, 2019

big thank you!!!!
Jason Miller August 5, 2019

Thanks for sharing such an useful information with us.
Michael April 11, 2024

Thanks for sharing it, if anyone is looking to develop sports betting app development for any type of betting app then please visit: https://www.apptunix.com/solutions/sports-betting-app-development-company/

Cookie	Duration	Description
cookielawinfo-checkbox-analytics	11 months	This cookie is set by GDPR Cookie Consent plugin. The cookie is used to store the user consent for the cookies in the category "Analytics".
cookielawinfo-checkbox-functional	11 months	The cookie is set by GDPR cookie consent to record the user consent for the cookies in the category "Functional".
cookielawinfo-checkbox-necessary	11 months	This cookie is set by GDPR Cookie Consent plugin. The cookies is used to store the user consent for the cookies in the category "Necessary".
cookielawinfo-checkbox-others	11 months	This cookie is set by GDPR Cookie Consent plugin. The cookie is used to store the user consent for the cookies in the category "Other.
cookielawinfo-checkbox-performance	11 months	This cookie is set by GDPR Cookie Consent plugin. The cookie is used to store the user consent for the cookies in the category "Performance".
viewed_cookie_policy	11 months	The cookie is set by the GDPR Cookie Consent plugin and is used to store whether or not user has consented to the use of cookies. It does not store any personal data.

Linglom.com

Enable Remote Connection on SQL Server 2008 Express

Step-by-step to enable remote connection on SQL Server 2008 express

336 Comments

Leave a Reply

Step-by-step to enable remote connection on SQL Server 2008 express

Related Posts

336 Comments

Leave a Reply