| 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 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
- Accessing SQL Server on NetBeans using JDBC, Part III: Troubleshooting
- Getting started with Microsoft Windows Server Update Services, Part I: Introduction
- How to backup and restore database on Microsoft SQL Server 2005
- Enable remote desktop on linux using VNC
- Getting started with Microsoft Windows Server Update Services, Part VI: Disconnected network(1)
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 name\Instance 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?