Enable Remote Connection on SQL Server 2008 Express
MS SQL Server, Security, Windows March 28th, 2009Introduction
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
- 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.
Related post
- Enable remote connection to SQL Server 2005 Express Introduction By default, SQL Server 2005 doesn’t allow remote connection so you have to enable it manually. If you try...
- How to Enable Remote Desktop Web Connection on Windows Introduction Remote Desktop Connection is one useful tool of Windows which allows you to manage remote servers so you don’t...
Related posts:
April 2nd, 2009 at 3:15 am
Thank you so much for publishing “Enable Remote Connection on SQL Server 2008 Express “. It was exactly what I needed.
Cheers,
Robert
April 7th, 2009 at 7:59 pm
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)
April 8th, 2009 at 10:45 am
Thank you very much for putting this tutorial together. It has been very helpful!
April 22nd, 2009 at 1:47 pm
Thanks!
April 22nd, 2009 at 6:33 pm
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.
April 30th, 2009 at 8:54 pm
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
May 5th, 2009 at 2:09 pm
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?
May 7th, 2009 at 9:47 am
It’s so great and very helpful!
May 12th, 2009 at 9:30 am
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
May 23rd, 2009 at 8:39 am
You are a good person. Thanks so much!
Eiad
June 4th, 2009 at 4:42 am
Thanx a lot mate, your tutorial helped me a lot…
June 4th, 2009 at 3:12 pm
Thanx man, it was very helpfull for me
keep the same way
June 15th, 2009 at 6:05 pm
Fantastic – you have saved me a lot of time – many thanks!
June 17th, 2009 at 5:40 pm
Thanks for this post. It is really helpful.
June 18th, 2009 at 10:25 pm
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
June 24th, 2009 at 4:19 pm
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
June 25th, 2009 at 9:20 am
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?
June 30th, 2009 at 7:23 pm
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.
July 2nd, 2009 at 12:25 am
Thanks so so much
July 9th, 2009 at 5:52 am
Thank you so much! Your posting was a huge help.
July 21st, 2009 at 4:29 pm
Thank you for both the 2005 and 2008 guide! Helped me with both as we have both at work!
July 23rd, 2009 at 5:23 am
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.
August 7th, 2009 at 3:11 am
excatly what i needed. you are awesome.
August 9th, 2009 at 7:10 am
Thanx to Turkey
August 15th, 2009 at 2:08 pm
Thanks. It is very helpful.
August 19th, 2009 at 5:28 pm
Thanks so so much to Turkey
August 23rd, 2009 at 7:14 pm
Thanks a lot for such a valuable info.
August 28th, 2009 at 2:23 am
WOW! thank you, thank you, thank you!!
September 8th, 2009 at 8:10 am
This is such a great write-up. Thanks!! so much for taking the time to pull this together.
September 14th, 2009 at 3:36 pm
Thank you so much!
October 7th, 2009 at 11:56 am
Thanks a lot… it solved my issue…..
October 8th, 2009 at 7:00 pm
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.
October 18th, 2009 at 9:48 pm
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.
October 19th, 2009 at 8:12 am
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.
October 19th, 2009 at 8:24 am
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???
October 25th, 2009 at 10:54 pm
Thanks.
It is very simple but very helpful.
October 26th, 2009 at 11:41 pm
@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!
October 27th, 2009 at 10:33 am
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.
November 6th, 2009 at 3:30 pm
Thank you for your nice and clean article.
November 19th, 2009 at 12:28 am
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.
November 20th, 2009 at 10:43 am
Hi,
Thanks a lot for this well explained tutorial along with screenshots. Appreciate your patience.
November 24th, 2009 at 12:31 pm
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…)
November 29th, 2009 at 4:48 am
Thanks! This was causing me much consternation.
November 30th, 2009 at 3:54 pm
thx for this solution
but i have WMI provider error
plz how can i connect to SQL server 2008 in my PC
December 10th, 2009 at 3:21 am
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
December 14th, 2009 at 3:15 pm
Thanks a lot. It is very helpful!
December 16th, 2009 at 9:42 pm
after your setting cannot my native client and sql service start
solve my problime
December 18th, 2009 at 11:24 pm
Thanks! I was about to reinstalll the whole sql 2008.
December 19th, 2009 at 2:50 am
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
December 30th, 2009 at 8:49 am
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.
December 30th, 2009 at 5:40 pm
Thank you very much… very nice and successful presentation.
January 3rd, 2010 at 9:00 am
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
January 8th, 2010 at 1:36 pm
Thanks…
January 11th, 2010 at 3:53 pm
thanks! very helpful indeed
January 15th, 2010 at 5:10 pm
I can enable the sql browser
btw im seeing two instance. a sqlexpress and mssqlserver
January 18th, 2010 at 12:11 am
thanks..
January 27th, 2010 at 1:19 am
Thanks a lot! Very helpful.
February 2nd, 2010 at 1:44 am
Thanks my friend!
February 5th, 2010 at 1:18 pm
thanx, u have explained it brilliantly
February 5th, 2010 at 7:15 pm
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
February 6th, 2010 at 5:36 am
Very Good Thanks
February 7th, 2010 at 12:35 am
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
February 7th, 2010 at 1:53 am
Gautam:
You need a user in SQLServer to login and the pass off course
February 7th, 2010 at 7:59 am
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
February 7th, 2010 at 10:27 am
Gautam that’s rigth, I’m sorry I should say: change of
February 7th, 2010 at 10:29 am
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)
February 7th, 2010 at 8:34 pm
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)”.
February 8th, 2010 at 1:51 pm
Daniel: why no connect by TCP / IP ???
February 8th, 2010 at 10:47 pm
Thanks a lot.. It was exactly what I was looking for. Much appreciated..
February 8th, 2010 at 11:09 pm
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.
February 11th, 2010 at 10:34 am
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: 8You 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.
February 18th, 2010 at 3:12 pm
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.
February 19th, 2010 at 1:41 am
mhmhmh, you should configurate the IIS to run the ReportServer to the port 80
February 23rd, 2010 at 10:40 am
I can’t thank you enough !
Excellent walkthrough .
February 25th, 2010 at 2:29 am
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 …
February 26th, 2010 at 5:53 am
Thanks! Clear and very helpful.