Enable remote connection to SQL Server 2005 Express
MS SQL Server, Security, Windows August 31st, 2007Introduction
By default, SQL Server 2005 doesn’t allow remote connection so you have to enable it manually. If you try to connect to remote SQL Server without enable remote connection first, you’ll see one of these error messages.
“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 connection. ….�?
“Login failed for user ‘sa’. The user is not associated with a trusted SQL Server connection.�?
To resolve these problems, you have 2 majors task. One is to enable both SQL Server and Windows authentication mode on SQL Server and the other is to allow remote connection and SQL Server Browser.
Step-by-step
- Check that you have allowed both SQL Server and Windows Authentication mode (If you’re not use Windows Authentication mode). For instance, user ‘sa’.
- Login to SQL Server using SQL Server Management Studio Express on local SQL Server with Windows Authentication user. This will use Windows account to authenticate to SQL Server.
- In Object Explorer, right click on the instance name and select Properties.
- On the left, select Security and change Server authentication to SQL Server and Windows Authentication mode. If the choice is already selected, that means you have already allowed SQL Server authentication.
- There’ll be an information window shows that you need to restart SQL Server to takes effect of the changes.
- Right-click on the instance name again, select Restart to restart SQL Server service.
- Select Yes.
- Wait for service stop and start.
- That’s it. You have enabled both SQL Server and Windows authentication so now you can login to SQL Server with your current Windows account or your created account.
- Check that you have enabled remote connection on SQL Server Surface Area Configuration
- Open SQL Server Surface Area Configuration.
- Select Surface Area Configuration for Services and Connections.
- On the left side, expand your SQL Server instance -> Database Engine -> Remote Connections. On the right side, select Local and remote connections -> using both TCP/IP and named pipes.
- On the left side, select SQL Server Browser -> Service.
On the right side, if the startup type is Disable, you need to change to Automatic and click Apply and click Start button to start the service. Then, click OK.
- You have finished configure. Now try to login to your SQL Server from remote computer using SQL Server authentication mode.
- Login to SQL Server succeeded.
Summary
This topic may be found on the Internet in many site or even Microsoft support but the purpose is to rearrange with easier to read and follow with graphic along with each step so it may help you reminder solution faster or even the person who have just start to use SQL Server 2005 Express Edition.
Related post
- Enable Remote Connection on SQL Server 2008 Express Introduction Last time, I wrote an article show how to enable remote connection on SQL Server 2005 Express. Now SQL...
- 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...
- Restore Database From SQL Server 2008 to SQL Server 2005, Part 2: Generate SQL Server Scripts Wizard Generate SQL Server Scripts Wizard On Part 1: Introduction, I mentioned about error messages when you try to restore a...
Related posts:
September 10th, 2007 at 7:07 pm
How can I set these configuration programmatically?
September 11th, 2007 at 6:20 pm
I’m not sure that you can do in that way because it may not safe. For instance, someone may try to reconfigure by execute script. Also, you need to restart SQL Server service which need right permission to do so.
November 9th, 2007 at 10:49 pm
You can set the login mode programatically – it’s just a registry key:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\MSSQLServer\LoginMode
(MSSQL.1 relates to the instance you want to play with – it could be MSSQL.2, or .3 etc).
It’s default value is 1. I set it to 2 in my installer to allow both windows auth and sql logins.
You have to restart the service before the changes kick in of course.
November 9th, 2007 at 10:59 pm
Oh, you’re right. I completely forgot that way.
Thanks for the tip, James.
April 14th, 2008 at 9:01 pm
Does anyone know of an sql script to do all this for you? ie. To enable both SQL Server and Windows authentication mode on SQL Server and to allow remote connection and SQL Server Browser. This way I can set it up automatically by using a batch file.
May 17th, 2008 at 2:37 am
This is my error when i try to login
TITLE: Connect to Server
——————————
Cannot connect to EASTSERVER\OFFICESERVERS.
——————————
ADDITIONAL INFORMATION:
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, Error: -1)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=-1&LinkId=20476
——————————
BUTTONS:
OK
——————————
May 18th, 2008 at 9:50 am
Hi, sowens
If you follow the link of this error message, you’ll find the possible cause and solution. But most of the time that I’ve seen this error is because of mistyped of the SQL Server name and instance name (SQL Server nameInstance name). Try to check the server name. If not that case, check that SQL Browser service is already started (on this post).
June 5th, 2008 at 9:43 am
[...] Enable remote connection to SQL Server 2005 Express | Linglom’s Blog Aktivasi server MSSQL untuk diakses jarak jauh. (tags: mssql server) [...]
June 20th, 2008 at 5:37 pm
thanks it was useful
July 18th, 2008 at 8:52 pm
I had installed the SQL server and it was accessed remotely using local network. Now it is possible to access from other machines using local network.
July 24th, 2008 at 2:27 pm
So is there a way to configure the sql express to allow remote connections programatically ?
Or at least from the install phase, but using ClickOnce and not unpacking the sqlexpr32.exe ?
July 25th, 2008 at 4:18 pm
Very Nice Information
Excellent representation of solution
My Work Done.
July 31st, 2008 at 1:38 am
hi, how to restart the database engine service?
July 31st, 2008 at 10:11 pm
See figure on step 5.
Or you can stop/start from the command-line:
To stop the MS SQL Server service, type “NET STOP MSSQLSERVER”
To start the MS SQL Server service, type “NET START MSSQLSERVER”
August 8th, 2008 at 4:18 am
I installed native client only on a machine and try to connect via windows authentication. It seems that the application can find the server but has no access to the database:
- unable to connect to database: Die von der Anmeldung angeforderte “HTWG”-Datenbank kann nicht geöffnet werden. Fehler bei der Anmeldung
- connection string: Provider=SQLNCLI.1;Integrated Security=SSPI;Persist Security Info=True;Initial Catalog=HTWG;Data Source=db01
If I change to SQL-authentification then it works fine!
Any ideas?
August 10th, 2008 at 8:30 pm
To Eherzel,
Have you check that you have create the correspond user which you used to authenticate on the SQL Server and give sufficient permission?
You can test the connection to SQL Server using SQL Server Management Studio from the client by using the same credential (user account) as when you run the code and try to connect to the SQL Server.
August 11th, 2008 at 6:07 pm
Server and client are in the same domain, users are logged in in this domain. Maybe the users need special rights? I tested too with Management Studio, I can connect to the server but have no right to access the database.
August 11th, 2008 at 8:28 pm
Check these issues:
1. Recheck your connection string again at here – http://www.connectionstrings.com/?carrier=sqlserver2005. You can copy and paste it to your code.
2. Try to give permission on the target user. If you’re testing, try to give full permission. But when finishes, don’t forget to change the permission to minimum privilege.
August 14th, 2008 at 11:19 pm
Here’s a way to do it from the commandline
http://www.dataportweb.com/post/Setting-up-sql-server-express-from-the-command-line.aspx
August 22nd, 2008 at 3:36 pm
The way of presenting the explantion is fantastic.
Thank you so much.
Thanks…
October 28th, 2008 at 11:24 pm
Thanks for the SQL Server and Windows Authentication step by step. It was very useful.
November 18th, 2008 at 11:55 pm
Great post exactly what I was looking for!
December 19th, 2008 at 12:56 am
I did a SQL 2005 instance installation but didn’t turn on the SQL Browser Service. It caused the application connection couldn’t resolve the named instance. After I turned on the SQL Browser Service. All are working fine. SOWENS’ link saved my day.
December 28th, 2008 at 9:34 pm
i m trying to connect .net with sql2005 but i m unable.
i hav created the object of sqlconnection as s and s.open() is showing error saying tat cant connect remotely with sql2005. i hav changed the setting still it is not working
January 9th, 2009 at 9:40 pm
I am trying, I hope this will help.
January 11th, 2009 at 9:22 am
To Atul Rai,
Try to connect to SQL Server using Management Studio from remote PC to see if you can access it or not. Sometimes the problem doesn’t come from programming. For example, firewall is blocking the port.
Simple way, try to telnet to the SQL Server with port 1433 (default SQL Server service port) remotely can also verify the connection to the SQL Server.
February 2nd, 2009 at 9:45 pm
I am preparing a website and first of all, I wish to express thanks for your kind advice about this contest of remote connexction that is a long headache to me.
February 6th, 2009 at 12:58 am
Hi, just wanted to say these instructions are a great help. Only thing is I couldnt login as sa without first enabling logins, in SMSS, Security, Logins. Dont know if it is just my ignorance or if instructions above could benefit, but thanx so much for them.
February 18th, 2009 at 9:30 am
It worked, thanks.
February 25th, 2009 at 3:51 am
Hi, thanks for detailed explanation on this very common problem.
March 16th, 2009 at 7:20 pm
hi there
i tried connecting using ur format
but it is not taking it. it is still giving error:18452
i have create user with name aman in server and tried to connect to the same user from client machine. but error: 18452:-)
kindly help if possible…..
March 18th, 2009 at 9:41 am
Hi, aman
-What’s the description of the error?
-Have you success to connect on locally?
March 18th, 2009 at 11:03 am
hi Sir
I am sorry. I was on mistake earlier. Actually i have made the mixed mode on client only,not on the server, thats why i was facing this error. Now my issue is resolved. Thanks a lot for your kind help.
cheers…
March 27th, 2009 at 2:04 am
You made my day. I was struggling with remote connection issues. Then landed on your site via google. Thanks for your tips with screenshots. Great illustration. I’m able to connect and work within a few mins.
Thanks so much for taking the effort in putting all this together.
April 6th, 2009 at 10:30 pm
hi, you are so cool, i was forgetting the initial step. thanks
May 22nd, 2009 at 12:14 pm
hai…its worksss….thankzzzzzzzz man….
May 22nd, 2009 at 10:05 pm
awesome job…..
May 25th, 2009 at 11:11 pm
Excellent !
June 1st, 2009 at 7:10 pm
How can I enable the SQL Remote Connections programmatically?
June 4th, 2009 at 6:49 pm
THis is all ok when it is in normal mode. My question is how to enable remote connection in cluster environments.
TITLE: Surface Area Configuration
——————————
You cannot configure surface area of clustered services by connecting to a computer name. Connect to the virtual server to configure clustered services.
——————————
BUTTONS:
OK
——————————
Tell me how to enable the same.
June 5th, 2009 at 11:59 am
I already follow your step to remote sql server……but still cannot to access…..the failure massage is default setting does not allow to remote connection
June 11th, 2009 at 9:02 pm
Hi, Pearl
See comment 3 on this post.
Hi, Saraboji Jayaraman
It seems that you are trying to configure Surface Area Configuration on a node not the whole cluster. The solution is to connect to the cluster. On Surface Area Configuration, you will see an option “Change Computer”, select Remote Computer and type in the your cluster name (virtual server). Now you should be able to access it.
Hi, Safuan
Can you show the error message in detail? Eg. The error message and its ID.
June 29th, 2009 at 10:42 am
Thanks for your topic
It’s so useful !
July 8th, 2009 at 8:12 pm
I was struggling with remote connection setup, landed your site via google. Thanks for the wonderful flow with screenshots. Now, I could successfully connect and work with Remote connection without any issue. Great illustration.
July 8th, 2009 at 8:38 pm
thank you for info. I thoght the mssqlserver2005 for trial version was made with an intent to cause users hard enough to buy a real version as a mareting skim.
August 7th, 2009 at 7:58 am
[...] Enable remote connection to SQL Server 2005 Express [...]
August 24th, 2009 at 11:40 pm
thanks. your tips really helps!
August 25th, 2009 at 9:35 pm
Here is my problem. I have 2 PC on Intranet and both have SQL SERVER Express installed. One PC(server) has mdf file in C:\data folder. I can browse to this file using Management studio from BOTH PC’s, using server PC name. I created Windows .NET program to access data. Works fine on server. When I compile and run on second PC(client) it gives me error:User does not have permission to perform this action. Both PC logged with same user id and password. Here is my connection string:
Data Source=MyServerPC\SQLEXPRESS;AttachDbFilename=c:\data\Teachers.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True
Can anyone help?
Thanks
September 2nd, 2009 at 1:22 pm
Hi, Al
Try this connection string instead.
Data Source=.\SQLExpress;AttachDbFilename=c:\mydbfile.mdf;Database=dbname; Trusted_Connection=Yes;
September 5th, 2009 at 4:58 am
Thanks for your help Linglom, I have worked with a number of people and searched on this problem with no avail. Your site popped up my last search, and showed me everything I needed to log on to the SQL Server. Outstanding Job!!!!
September 17th, 2009 at 11:23 am
I just found the solution, thanks for your useful information. Now i am playing around the ASP.NET and SQL server.
Regards,
October 9th, 2009 at 4:38 am
Hi,
I am having different problem.
I can connect to my local sqlserver from one machines but not from other mahcine.
I did the setup for configuring server for remote access including allowing remote connection, having the sqlbrowser service setup and creating firewall exceptions for the sqlsrvr and sqlbrowser executables.
I am clueless why it is not able to connect from other machine.
Can someone help me.
Vishnu
October 10th, 2009 at 2:27 am
Hi…
I doing the instruction,but it still not working…
“Microsoft SQL Server, Error:233″
i already check and repeat that instruction, but the error message still same…
can someone know my problem ?
November 6th, 2009 at 2:52 am
This is causing me to pull my hair out.
I have SQL Server 2005 Express intalled on two different WHS servers.
I have two clients I am trying to access them from: a desktop and a laptop, both with Vista Ultimate.
From the desktop I have no problem access them both through the SQL Server 2005 Management Studio Express.
From the laptop I CANNOT access either of them through the SQL Server 2005 Management Studio Express. I keep getting the dreaded error 26.
I cannot find anything that would block access. HELP!!!!!
November 9th, 2009 at 11:41 am
Hi, Rufus
Can you show detail of the error message?
Hi, Rctaubert
If you can access the servers from desktop, this means that the remote configuration is work fine. So the problem should be on the network between laptop and the servers or the laptop itself. Check these issues:
- Make sure the server name is typed correctly.
- Is the laptop on the same network as the desktop? Is there any additional firewall blocking the traffic?
- Is the firewall on laptop is blocking the connection? You can try to disable it and test.
November 9th, 2009 at 9:20 pm
Thank you for your response.
In both cases I am using SQLServer 2005 Server Management Studio Express to connect. In either instance the Management studio is able to detect both servers and SQLExpress instances.
I had already compared the Firewall settings between the laptop and desktop. I have a few more apps installed on the laptop so there are a few more exceptions, but other than that they are pretty much the same.
All my computers are on my local network and all nics are set up the same.
Already tried disabling the firewall on both WHS and the laptop. No joy!
I had pretty much poured over all the forums I could find and tried all the usual suggestions.
This is what is so frustrating. There does not appear to be a logical reason for this to happen. In fact, before I set up the second server I was able to access SQLExpress from my laptop.
November 11th, 2009 at 2:32 pm
Hi
I want to install client tools in clinet machine ?How to do?
November 13th, 2009 at 10:23 am
Thanks.
Nicely presented and very helpful.
November 13th, 2009 at 1:20 pm
Hi Sir
I have sql server 2005 installed in my PC. Now i want to connect this sql server 2005 instance via client tool to other pc and my head office located in other region. How to connect?
November 17th, 2009 at 9:38 am
Hi, rctaubert
Your problem is quite weird. You told that before setting the second server you can access SQL Server from the laptop. Then, why don’t you try to connect the laptop and the SQL Server directly (direct connection) to see if it is still connect-able?
Hi, Aruna
To install only client tool, run setup for SQL Server and when it is on feature select page, you choose to install only Client Components -> Management Tools.
For second question, if you want to connect the SQL Server from remote PC through the Internet, the server must have static IP. Or you can use the dynamic DNS service from http://www.no-ip.com/.
November 22nd, 2009 at 10:20 am
Thank you so much.
I have the same problem and have been trying for a long time, but haven’t found the solution yet.
Your work do help me a lot.
(Sorry for my bad English)
December 11th, 2009 at 3:14 pm
OLE DB provider ”SQLNCLI” for link server ”(null)” returned message ”Login timeout expired”
December 30th, 2009 at 9:13 am
Hi, Gaul
This could be useful, see How do I prevent linked server errors?
January 23rd, 2010 at 12:27 am
I am not able to connect to the server in same network, can any one please help me as soon as possible. i tried the above process
January 25th, 2010 at 11:03 am
Hi, ABHI
Try to test connection from client to the SQL Server by using telnet command:
If you can connect, it will show a blank screen. Otherwise, the connection may be blocked by some firewall or router.
February 18th, 2010 at 12:34 pm
Hi,
I received same error which is in the second screenshot i tried to all modification which you have mention but the status of Tcp\IP port is not getting change in configuation manger and also i tried enable in surface area confiration also here also status is changing.
February 23rd, 2010 at 9:23 am
Hi, Amar
What do you mean “the status of Tcp\IP port is not getting change”? Can’t you enable TCP/IP?
Is there any error message?
March 16th, 2010 at 8:16 pm
i appreciate it was very helpful
March 26th, 2010 at 7:09 pm
thats great!, it worked for me
April 17th, 2010 at 7:34 pm
Thanks for the great work and narration, perfectly worked….Thanks once again…… Rajesh R
April 22nd, 2010 at 12:33 am
for allowing remote connections …i go through steps……my problem is ["restart database Engine"]
how do i finish it…….
April 27th, 2010 at 11:43 pm
Nice topic, it really work for me! after finished all settings just restart computer and try to connect again (don’t forget to turn off firewall).
April 28th, 2010 at 8:29 am
nice furom man! it works! keep it up!
June 9th, 2010 at 4:37 am
I am using local machine for sqlserver 2005 express. i have tried for connection but unable to do. Getting error “provider: SQL Network Interfaces, error: 25 – Connection string is not valid”. I am using connection string as
“Data Source=MYCOMP/SQLExpress, Integrated Security=True, database=Mydata”. Please help me…
June 17th, 2010 at 8:30 pm
Thanks
June 18th, 2010 at 10:48 am
Hi, Prabhakar
I suggest you review your connection string again. See Connection strings for SQL Server 2005
July 2nd, 2010 at 1:32 pm
Hi
I’m having the same problem as some other people on this site. I’m having a sql2005 express installation on one computer. I can access it remotly through management tool and through telnet. But when I try to connect an application to the database the error verify that you have remote connection enable is showing. And in the eventviewer logs for database does not exists shows. Why? it’s obviously there. By the way, the application is Backup Exec 2010.
Mike
July 13th, 2010 at 10:22 am
hi,
I want to connect sql server via remote desktop connection.It is on the other server. Just now I don’t get connect with this server. I get a connection between my
computer and host computer. But I can not access sql server 2008. How should I do?
Thanks,
ToeToeAung
July 14th, 2010 at 1:32 pm
Hi Linglom,
I’m using sql2005. I’m facing the problem with remote server connection. I have changed the Server authentication to “SQL Server and Windows Authentication mode”. But in the case of Surface Area Configuration, I could not change Remote connections to “Using both TCP/IP and named pipes”. Please help me…
The error message showing :
Alter failed. (Microsoft.SqlServer.Smo)
ADDITIONAL INFORMATION:
SetEnable failed for ServerProtocol ‘Tcp’. (Microsoft.SqlServer.Smo)
Access denied (System.Management)
July 14th, 2010 at 5:30 pm
Great Work.. Thanks
July 15th, 2010 at 10:21 am
Hi, Mike
If you can access SQL Server on remote PC, you should be able to use it with any application remotely. Check that you have type the server instance correctly when connecting. Or try to ask this question in Symantec forum.
Hi, ToeToeAung
I don’t quite understand your question. If you remote desktop to the server, you will be as local on that server.
Hi, Asif Kannattil
Did you have administrator permission on the server? It could be permission issue.
August 5th, 2010 at 12:42 pm
great information….
it solved my problems thanks
keep imparting good knowledge