none
How to allow remote connections to SQL Server 2005 Express database

    Question

  • I've developed an asp.net 2.0 web app with vs 2005 and am using a SQL Server 2005 Express database.  The app works fine locally, but after uploading to the remote web server the following error occures:

    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)

    How do I go about granting remote connections to SQL Server Express?

    Does the web server have to have SQL Server Express installed in order to run apps that utilize SQL Server 2005 Express databases?

    Do I grant access locally on my machine or do I have to be on the web server to grant remote connections?

    I've been using .net 1.1 with access databases for the past couple of years, so this is all new to me.

    Thanks.

    Monday, February 27, 2006 6:47 PM

Answers

  • You will need to enable TCP/IP or Named Pipes on the SQL server and check if the firewall is enabled and open up the appropriate ports.

    To enable TCP/IP or Named Pipes:

    1. From the Start menu, choose All Programs, point to Microsoft SQL Server 2005, point to Configuration Tools, and then click SQL Server Configuration Manager.
      Optionally, you can open Computer Manager by right-clicking My Computer and choosing Manage. In Computer Management, expand Services and Applications, expand SQL Server Configuration Manager.
    2. Expand SQL Server 2005 Network Configuration, and then click Protocols for InstanceName.
    3. In the list of protocols, right-click the protocol you want to enable, and then click Enable.
      The icon for the protocol will change to show that the protocol is enabled.

    To open the ports on the firewall:

    1. Click Start, click Control Panel, and then click Network  Connections.
    2. From the navigation bar on the left, click Change Windows Firewall settings.
    3. On the Exceptions tab, in the Programs and Services box, you will probably see that SQL Server is listed, but not selected as an exception. If you select the check box, Windows will open the 1433 port to let in TCP requests. Or 445 if you want to connect over named pipes. Alternatively, if you do not see SQL Server listed, do the following:

      a. Click Add Program.
      b. Click Browse.
      c. Navigate to drive:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\BINN
      d. Add the file sqlservr.exe to the list of exceptions.

    Hope this helps

    Brad Sarsfield

    Tuesday, February 28, 2006 12:57 AM
  • Hi Ahmed,

    Is your error exactly the same as the one originally described in this thread?  i.e.,

    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)

    If this is the case, then the problem is that the client is unable to contact SQL Browser.  You need to (i) make sure that the SQL Browser service is started on the server, and (ii) the firewall is configures to make an exception for the SQL Browser service (typically \Program Files\Microsoft SQL Server\90\Shared\sqlbrowser.exe) or alternatively make an exception for UDP 1434.

    Additional information about SQL Browser is available in the BOL: http://msdn2.microsoft.com/en-us/library/ms165724.aspx

    If this isn't your problem, then please provide the exact error message that you're receiving.

    Thanks,
    Il-Sung.

    Tuesday, September 19, 2006 4:32 PM

All replies

  • You will need to enable TCP/IP or Named Pipes on the SQL server and check if the firewall is enabled and open up the appropriate ports.

    To enable TCP/IP or Named Pipes:

    1. From the Start menu, choose All Programs, point to Microsoft SQL Server 2005, point to Configuration Tools, and then click SQL Server Configuration Manager.
      Optionally, you can open Computer Manager by right-clicking My Computer and choosing Manage. In Computer Management, expand Services and Applications, expand SQL Server Configuration Manager.
    2. Expand SQL Server 2005 Network Configuration, and then click Protocols for InstanceName.
    3. In the list of protocols, right-click the protocol you want to enable, and then click Enable.
      The icon for the protocol will change to show that the protocol is enabled.

    To open the ports on the firewall:

    1. Click Start, click Control Panel, and then click Network  Connections.
    2. From the navigation bar on the left, click Change Windows Firewall settings.
    3. On the Exceptions tab, in the Programs and Services box, you will probably see that SQL Server is listed, but not selected as an exception. If you select the check box, Windows will open the 1433 port to let in TCP requests. Or 445 if you want to connect over named pipes. Alternatively, if you do not see SQL Server listed, do the following:

      a. Click Add Program.
      b. Click Browse.
      c. Navigate to drive:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\BINN
      d. Add the file sqlservr.exe to the list of exceptions.

    Hope this helps

    Brad Sarsfield

    Tuesday, February 28, 2006 12:57 AM
  • Brad, thanks for the info. Just need some clarification.

    1.) Do I need SQL Server Express installed on the remote web server where the web application resides?  I'm used to Access where we just upload the database files and set the connection string and we're good to go.

    2.) Do I make your settings changes on the remote web server or on my development machine?  If on the remote web server, I would then assume that It would need to have SQL Server Express installed on it.

    Thanks again with your help on this.  I'm used to using Access with my web apps so I'm alittle lost with the deployment and configuration of SQL Server Express.

    Mark

     

    Wednesday, March 01, 2006 12:29 AM
  • 1) SQL Server Express can be either on your Web Server or on a seperate database server.  It's all in the connection string.  It all depends on the load that your app will take; chances are apps build with Express you can quite easily host Express on your web server box.

    2) You have to make the changes on the same machine as where you have SQL Server Express installed.

    Brad

     

    Wednesday, March 01, 2006 5:47 PM
  • Ok, now I'm starting to understand.  Where ever I have my database, I have to have SQL Express installed as well, in this case the remote web server where the web application and database will reside.

    I have to configure SQL Express on the remote web server to accept remote connections as you mentioned above.

    I'm clear with the above.  Here's another question:

    Do I have to do anything special with my Express database once I upload/copy to the remote web server(i.e., register/connect to it inside of Sql Express service)? Or does it end after I upload it.

    Thanks.

    Thursday, March 02, 2006 11:52 AM
  • Deleted
    Friday, March 24, 2006 8:29 PM
  • Well, here's my story then.

    We had an old server running .NET BETA 2, I finally got around to upgrading it.  Lo-and-behold, the only site I used the ASP.NET Roles system for won't work, and I receive the following error:

    "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)"

    Tried using the steps above, but SQL Server Express Manager crashes whenever I try to open it - already tried a re-install, got the latest CTP, and that one doesn't COME with a Manager!

    Monday, March 27, 2006 8:21 PM
  • Try this

    http://support.microsoft.com/default.aspx?scid=kb;EN-US;914277

    Its exactly what explained here, except 1 part. You have to put another exception into Firewall for SQL Browser. It helped me. Maybe it will help you too ;)

    Monday, March 27, 2006 8:39 PM
  • That's the problem.

    I don't have SQL Server Configuration Manager for some reason or another.  The one that came with the express edition just crashes right away.
    Monday, March 27, 2006 8:43 PM
  • Did you try to disable Firewall?
    Monday, March 27, 2006 8:45 PM
  • I don't know about this kind of problem. I downloaded SQL Express from microsoft and everything works properly. Maybe you should download it again.
    Monday, March 27, 2006 8:49 PM
  • Hm, just tried that too.  No beans.  I've started to move the database over to our actual SQL Server.  New error:

    The 'System.Web.Security.SqlMembershipProvider' requires a database schema compatible with schema version '1'.  However, the current database schema is not compatible with this version.  You may need to either install a compatible schema with aspnet_regsql.exe (available in the framework installation directory), or upgrade the provider to a newer version.

    I  ran aspnet_regsql.exe, went through the steps to update or create a new database, both.  Same error, for both.  Don't know what to do - the customer's website has been down all weekend because of this.
    Monday, March 27, 2006 9:56 PM
  • Finally managed to get the Configuration Manager to work.  Had to install Visual Studio on the SERVER.  After giving the server permission to access itself locally, here's my most recent error:

    Could not find stored procedure 'dbo.aspnet_CheckSchemaVersion'.

    Not sure what to do with it.  It SHOULD be accessing an MDB.  Absolutely everything back in the state it was when it was running .NET BETA 2.0.
    Tuesday, March 28, 2006 1:43 AM
  • Hmm, did you check if your Server match rerquirments for SQL Express. Look at this:

    http://download.microsoft.com/download/f/1/0/f10c4f60-630e-4153-bd53-c3010e4c513b/RequirementsSQLEXP2005.htm

     

    Another solution:

    May be you have some junk on computer. First, maybe you should check how SQL Express works on "fresh" environment. For example, take new computer or create virtual PC, and only thing, except Windows itself, will be SQL Express and its managment studio.

    Tuesday, March 28, 2006 1:46 PM
  • Hi

    I have the same problem..

    I'll tell what happend and hope to help me...

    I installed SQL Express on my PC and also SQL 2000, and they were working fine tohether for months

    And then out of no where SQL Express stoped, i found out there's no more connection with any DB at any application.

    Espacially when I'm using Visual Web Developer and trying to work on ASP.NET Configutration, and when i click on Security tab, it showed message , there's no SQL DB, and it faild to conennect to any DB.

    Afetr that i un-istall SQL Express and installed SQL Standerd ( i thought i have problem with Express ), but still not working and the same problem still exist..

    When i followed your steps i found out that i don't have any item under SQL Server 2005 Network Configuration.... I don't why.

    I don't know what happend..

    Any Help PLEASE..

    Thanks

    Ahmed

    Friday, August 25, 2006 7:31 AM
  • Hi Brad,

    I read your answer above. Thank you for posting all of those details.  My problem is the same as the original posted.  The only difference is that I get error message on my local machine.  I downloaded VS studio express, and created a personal website using the wizard to test drive the thing.  When I hit ctr + f5 to run the sample provided, then I get this message:

     

    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)

     

    After reading your response, I tried disabling the firewall (norton's firewall because Windows is disable always).  Any suggestions?  This is a brand-new machine, running XP sp2, just downloaded the VS studio express.

    I really appreciate your inputs.

    Regards,

    Carlos

    info@paesano.com

    Monday, September 18, 2006 11:03 AM
  • Hi Ahmed,

    Is your error exactly the same as the one originally described in this thread?  i.e.,

    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)

    If this is the case, then the problem is that the client is unable to contact SQL Browser.  You need to (i) make sure that the SQL Browser service is started on the server, and (ii) the firewall is configures to make an exception for the SQL Browser service (typically \Program Files\Microsoft SQL Server\90\Shared\sqlbrowser.exe) or alternatively make an exception for UDP 1434.

    Additional information about SQL Browser is available in the BOL: http://msdn2.microsoft.com/en-us/library/ms165724.aspx

    If this isn't your problem, then please provide the exact error message that you're receiving.

    Thanks,
    Il-Sung.

    Tuesday, September 19, 2006 4:32 PM
  • Hi,

    Your post was really helpful to me and I was able to allow remote connections.

    But I have a problem is deployment. My client is il-literate in computers and will not be able to do this after installation.

    Is there any way where in I can "Enable TCP/IP ports" & "Enable & Start SQL Browser service" from my C# code, which I can place in my installer class.

    Thanks in advance.

    Regards,

    Mandar Purohit

     

     

     

     

    Friday, September 22, 2006 5:36 AM
  • Hi Mandar,

    You can configure your server remotely using WMI: http://msdn2.microsoft.com/en-us/library/ms180606.aspx

    Ju-Yi has written up a quick explanation of using WMI in this blog: http://blogs.msdn.com/sql_protocols/archive/2005/10/19/482840.aspx

    Hope this helps,

    Il-Sung.

    Monday, September 25, 2006 10:29 PM
  •  i receive the following error:

    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: Named Pipes provider, error: 40 - could not open a connection to SQL server)

    bearing in mind that i enabled remote services from SQL server configuration manager and i check the sqlsrvr in the exections for windows firewall

    by the way i am working on SQL 2005 server express database but i still have SQl 2000 installaed on my pc. usually i stop the service manager of SQL 2000 and then i connect SQl 2005 and it was working but suddenly i get the error 

    plz help

    Wednesday, September 27, 2006 12:50 PM
  •  Pavel Brokhman wrote:

    . . .Another solution:

    May be you have some junk on computer. First, maybe you should check . . .


    This should not work.
    The error is occured due a bug in Visual Studio 2005.
    It leads in such problems when deploying data-driven applications.

    As I know , it will not be fixed before Orcas releases.
    Tuesday, April 17, 2007 9:52 AM
  •  Pavel Brokhman wrote:

    ...  You have to put another exception into Firewall for SQL Browser. It helped me. Maybe it will help you too Wink



    I just want to add that the SQL Browser service might be disabled by default. Then you would have to enter Administrative Tools / Services and start it (Set it to manual and start, or let it be started automatically).

    // Andreas
    Wednesday, April 25, 2007 8:10 AM
  •  Pavel Brokhman wrote:

    Try this

    http://support.microsoft.com/default.aspx?scid=kb;EN-US;914277

    Its exactly what explained here, except 1 part. You have to put another exception into Firewall for SQL Browser. It helped me. Maybe it will help you too Wink



    This is a very good link! It's worth to point out specifically that the SQL Browser is disabled by default, and that you will have to start it on the "server instance" to be able to find it from another computer in your network.
    Wednesday, April 25, 2007 8:39 AM
  •  Pavel Brokhman wrote:

    Try this

    http://support.microsoft.com/default.aspx?scid=kb;EN-US;914277

    Its exactly what explained here, except 1 part. You have to put another exception into Firewall for SQL Browser. It helped me. Maybe it will help you too Wink



    A very good link! It's worth to specifically point out that SQL Browser is disabled by default, and that you will have to start it.
    // Andreas
    Wednesday, April 25, 2007 8:42 AM
  • I tried this, but it didn't fix the problem.  Is there something else?  It seems that at one point I saw a setting in the configuration manager to allow remote connection, but I can't find it now.

     

    Here is the complete error message.

     

    System.Data.SqlClient.SqlException was unhandled
      Class=20
      ErrorCode=-2146232060
      LineNumber=0
      Message="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: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)"
      Number=2
      Server=""
      Source=".Net SqlClient Data Provider"
      State=0
      StackTrace:
           at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
           at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
           at System.Data.SqlClient.TdsParser.Connect(ServerInfo serverInfo, SqlInternalConnectionTds connHandler, Boolean ignoreSniOpenTimeout, Int64 timerExpire, Boolean encrypt, Boolean trustServerCert, Boolean integratedSecurity, SqlConnection owningObject)
           at System.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo serverInfo, String newPassword, Boolean ignoreSniOpenTimeout, Int64 timerExpire, SqlConnection owningObject)
           at System.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(String host, String newPassword, Boolean redirectedUserInstance, SqlConnection owningObject, SqlConnectionString connectionOptions, Int64 timerStart)
           at System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance)
           at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance)
           at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection)
           at System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnection owningConnection, DbConnectionPool pool, DbConnectionOptions options)
           at System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject)
           at System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject)
           at System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject)
           at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)
           at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
           at System.Data.SqlClient.SqlConnection.Open()
           at DatasetExample.Form1.Form1_Load(Object sender, EventArgs e) in C:\Users\Tom Magaro\Documents\Visual Studio 2005\Projects\DatasetExample\DatasetExample\Form1.vb:line 25
           at System.EventHandler.Invoke(Object sender, EventArgs e)
           at System.Windows.Forms.Form.OnLoad(EventArgs e)
           at System.Windows.Forms.Form.OnCreateControl()
           at System.Windows.Forms.Control.CreateControl(Boolean fIgnoreVisible)
           at System.Windows.Forms.Control.CreateControl()
           at System.Windows.Forms.Control.WmShowWindow(Message& m)
           at System.Windows.Forms.Control.WndProc(Message& m)
           at System.Windows.Forms.ScrollableControl.WndProc(Message& m)
           at System.Windows.Forms.ContainerControl.WndProc(Message& m)
           at System.Windows.Forms.Form.WmShowWindow(Message& m)
           at System.Windows.Forms.Form.WndProc(Message& m)
           at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m)
           at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)
           at System.Windows.Forms.NativeWindow.DebuggableCallback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)
           at System.Windows.Forms.SafeNativeMethods.ShowWindow(HandleRef hWnd, Int32 nCmdShow)
           at System.Windows.Forms.Control.SetVisibleCore(Boolean value)
           at System.Windows.Forms.Form.SetVisibleCore(Boolean value)
           at System.Windows.Forms.Control.set_Visible(Boolean value)
           at System.Windows.Forms.Application.ThreadContext.RunMessageLoopInner(Int32 reason, ApplicationContext context)
           at System.Windows.Forms.Application.ThreadContext.RunMessageLoop(Int32 reason, ApplicationContext context)
           at System.Windows.Forms.Application.Run(ApplicationContext context)
           at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.OnRun()
           at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.DoApplicationModel()
           at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.Run(String[] commandLine)
           at DatasetExample.My.MyApplication.Main(String[] Args) in 17d14f5c-a337-4978-8281-53493378c1071.vb:line 81
           at System.AppDomain._nExecuteAssembly(Assembly assembly, String[] args)
           at System.AppDomain.ExecuteAssembly(String assemblyFile, Evidence assemblySecurity, String[] args)
           at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()
           at System.Threading.ThreadHelper.ThreadStart_Context(Object state)
           at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
           at System.Threading.ThreadHelper.ThreadStart()

    I'm at wits end on this, I've been trying for a week to get it right!

    Saturday, January 12, 2008 5:24 PM
  • 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: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)


    I've followed the above thread (Brad Starsfield) and the MS KnowledgeBase article found at http://support.microsoft.com/kb/914277 - I still come back to the above error!

    For me this is 2005 specific as connected to the same db in SQL Server 2000 works fine. What's more is if i make connection through VS.NET 2005 GUI to a SQL Server 2005 db that also works fine. it's only when do it programmatically that it doesn't;grhhh)

    I am working on a WSS 3.0 solution creating c# dlls which is why this will become critical that i solve this. Any ideas would be much appreciatedWink
    Tuesday, April 01, 2008 5:46 PM