次の方法で共有

How to: Configure Express to accept remote connections

  • [アーティクル]

Updated

The information in this posting is superseded by the following KB Article:

914277 How to configure SQL Server 2005 to allow remote connections
https://support.microsoft.com/default.aspx?scid=kb;EN-US;914277

-----------------------------------------------------------------------------------

Some people have been having issues when trying to make remote connections
to SQL Express.  This document will hopefully clarify most of the issues
around remote connections.

First, networking protocols are disabled by default in SQL Server Express.
Thus, if someone simply installs Express and chooses all the defaults, SQL
Server Express will only be able to have connections originating on the
local machine where SQL Server is installed.

To enable SQL Server Express to accept remote connections we need to perform
the following steps:

STEP 1: Enabling TCP/IP

First we must tell SQL Server Express to listen on TCP/IP, to do this
perform the following steps:

1. Launch the SQL Server Configuration Manager from the "Microsoft SQL
Server 2005 CTP" Program menu
2. Click on the "Protocols for SQLEXPRESS" node,
3. Right click on "TCP/IP" in the list of Protocols and choose, "Enable"

STEP 2: To Browse or not to Browse

Next, we have to determine if we want the SQL Browser service to be running
or not.  The benefit of having this service run is that users connecting
remotely do not have to specify the port in the connection string.  Note: It
is a security best practice to not run the SQLBrowser service as it reduces
the attack surface area by eliminating the need to listen on an udp port.

OPTION A: If you want to always specify a TCP port when connecting (Not
using SQL Browser service) perform the following steps else skip these
steps:

1.      Launch the SQL Server Configuration Manager from the "Microsoft SQL
Server 2005 CTP" Program menu

2.      Click on the "Protocols for SQLEXPRESS" node

3.      Click on the "TCP/IP" child node

4.      You will notice an entry on the right panel for "IPAll", right click
on this and select, "Properties"

5.      Clear out the value for "TCP Dynamic Ports"

6.      Give a TcpPort number to use when making remote connections, for
purposes of this example lets choose, "2301"

At this point you should restart the SQL Server Express service.  At this
point you will be able to connect remotely to SQL Express.  A way I like to
check the connection is my using SQLCMD from a remote machine and connecting
like this:

SQLCMD -E -S YourServer\SQLEXPRESS,2301

The "," in the server name tells SQCMD it's a port.

So you've tried this and still get an error.  Take a look at Step 3, this
should address the remaining issue.

OPTION B:  If you want to use SQL Browser service perform these steps:

            Note:
You will need to make this registry key change if you are using the April
CTP or earlier versions:

            To enable sqlbrowser service to listen on the port 1434, the following
registry key must be set to 1
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\90\SQL
Browser\Ssrplistener

            Next, restart the sqlbrowser service.

      1. Start the SQL Browser Service

STEP 3: Firewall..?

      At this point you should be able to remotely connect.  If you still
can't chances are you have a firewall configured on the computer where SQL
Express is running.  The instructions below are for Windows XP SP2's
firewall settings.

      To enable the firewall to allow SQL Server Express traffic:

1.      Launch the Windows Firewall configuration tool from the control
panel.

2.      Click the Exceptions Tab

3.      Click the "Add Programs." button and select "sqlservr.exe" from the
location where you install SQL Server Express

You should be able to remotely connect.  Note, you can get more restrictive
by just specifying the port number that will be allowed (used best when
configured with Option A).

Note: If you chose to use the SQL Browser service, you must also add
sqlbrowser service executable to the exception list as it listens on udp
port 1434.

Comments

  1.  Sql Server Configuration manager:        SQL Server network Configuration:   a) Enable NP (Named Pipes) b) Enable TCP/IP Properties in TCP/IP: Dynamic Port (removed "0")                        Set Port to:  2309 (for IP1 through IPall)        Restarted SQLExpress for settings to take effect.
  2.  SSMS:        a) Set Security, authentication to:  SQL Server and Windows Authentication Mode (set sa password).        b) Security, Logins:  verified the "sa" account was in the list
  3.  Firewall:  Added to exception list, leaving the "scope" as the default for testing:        a) SqlServer.exe        b) SqlBrowser.exe

  • Anonymous
    January 29, 2011
    Very nice info. Thank you for your post. www.drwebworks.com

  • Anonymous
    January 30, 2011
    Thanks..! excellent worked for me after i added windows firewall exception for TCP port 2301.

  • Anonymous
    August 01, 2011
    The comment has been removed

  • Anonymous
    August 27, 2011
    If you need to check whether your sql server is accessible online, you can try using the following tool online: www.webkeet.com/sqlservertestconnector.aspx

  • Anonymous
    October 17, 2011
    What does remote connection refre to.. DOes it mean sharing on sql server database over the LAN or over the internet as well... I have configured SQL server to allow access over the lan (computers connected to a common router can share database with each other).. Now i want to connect it over the internet.. while connecting i used to use ip-192.168.1.109... What will be the ip to connect it over the internet.. Or is that possible??

  • Anonymous
    November 17, 2011
    Very useful... nice and clean.

  • Anonymous
    December 02, 2011
    Muchas gracias. Funciona a la perfeccion

  • Anonymous
    June 29, 2012
    In the 90 folder (HKEY_LOCAL_MACHINESOFTWAREMicrosoftMicrosoft SQL Server90), i found three items. In reference to option b, should any of these items be changed or any files added. Those three are Default (value not set) SharedCode VerSpecificRootDir

  • Anonymous
    August 23, 2012
    thaaaaaaaanks so much :D they regkey solved my problem

  • Anonymous
    October 15, 2012
    it worked like a charm..!! thanks again.

  • Anonymous
    January 30, 2013
    Very nice info. Thank you for your post. <a href="http://www.cvorneklerim.com">cv örnekleri</a>

  • Anonymous
    April 09, 2013
    It worked. Thanks very much you saved my day... www.mindarraysystems.com

  • Anonymous
    September 03, 2013
    Changes to server properties and settings may affect the performance, security, and availability of this SQL Server instance. Before making any such changes, consult the product documentation.

  • Anonymous
    November 07, 2013
    Very nice article brother it works now in my system (Win 7 & XP). However, i'm trying to use Asp.net webservice using IIS 6.0 in windows 7. I'm able to add web reference but after adding i'm not able to access my database sql express 2005. it says Login Failed: database is read-only. Please help me with this if any one know. i'll be very greatfull to you guys.

  • Anonymous
    August 05, 2014
    The comment has been removed

  • Anonymous
    October 12, 2014
    Cannot connect server in sql server 2005 so error is a network-related or instance specific error occured 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:namedpipesprovider error:40 could not open a connection to sql server) microsoft sql server error 2 so plz help me

  • Anonymous
    April 08, 2015
    Worked for me on SQL Express 2014 and saved a lot of time over talking to the DB guys, thanks!

  • Anonymous
    May 22, 2015
    Also worth noting that in order to connect with a sql authentication the database settings must be set to allow Sql Server Authentication mode in Database Properties -> Security -> Server Authentication.