xFors


Hi,

I installed SQL server 2005 express edition on my windows server and I had no problem with connecting it remotely through Management Studio, then I removed it and installed a full edition SQL server 2005. That's when it's gone all wrong.

Now I can't remotely connect the server through Management Studio. The error I get is the same as some of the people in this forum has which is:

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

The problem is that I have all protocols enabled and even when I disable firewall and unassign all the ip filters still no go.

I looked at te machine.config file at my server and the connection string bit goes like this:

<connectionStrings>
<add name="LocalSqlServer" connectionString="data source=.\SQLEXPRESS;Integrated Security=SSPI;AttachDBFilename=|DataDirectory|aspnetdb.mdf;User Instance=true" providerName="System.Data.SqlClient"/>
</connectionStrings>

Can anybody tell me wether you think the problem lies here where it says SQLEXPRESS where I actually have Sql Server 2005 installed.

I've been in and out of every documentation regarding this but they all tell me to configure the protocols and nothing else which doesn't really help.

I have tried running .Net Sql server wizard on the server but it didn't connect probably for the same reasons why I cant connect remotely.

I would really appreciate any help

Thanks




Re: SQL Server 2005 Remote Connection Problem... Big Time.

Peter Gvozdjak - MSFT


(1) Your data source specifies a SQL Server on the local machine - the dot represents the local machine. If you want to connect to a remote machine you must replace it with the remote machine's name.

(2) Depending on how you installed the full edition of SQL Server 2005 you may need to either replace or remove "\SQLEXPRESS" from the data source specification.

In summary,

(a) If you installed the full edition as a default instance you should use data source similar to this:

data source=myRemoteMachine

(b) If you installed the full edition as a default instance you should use data source similar to this

data source=myRemoteMachine\myInstance

where myInstance may be equal to SQLEXPRESS if that's the name of the instance.







Re: SQL Server 2005 Remote Connection Problem... Big Time.

xFors

Thanks for your reply,

1 - Am I supposed to modify the machine.config on the server or client

2 - if I have to modify it on client can "myRemoteMachine" be the servers IP or server name

I am very confused

Thanks Again






Re: SQL Server 2005 Remote Connection Problem... Big Time.

LU MING

Did you install SQL Server 2005 as a default instance or a named instance If named instance, what is the instance name, "SQLExpress" The error indicates that eiter you specify the wrong instance name or your sqlbrowser on your server side was not started.

To resolve this:

1) Since you were trying remote connection, you need to change your connection string to "data source=<RemoteServerName>\<InstanceName> ...." <InstanceName> is the one you specified during installation of SQL Server 2005.

2) Go to your remote server, run " net start " or go to services, see whether sqlbrowser service was started.

Thanks!






Re: SQL Server 2005 Remote Connection Problem... Big Time.

??r?s而考老h旨r Sc?汛l而缶灰

Two things to note:
  1. Named Pipes does not work when configured as the only clientconnectivity option for ASP.NET running in default circumstances. Havea look at the following article:

    BUG: Named pipes do not work when workerprocess runs under ASPNET account
    http://support.microsoft.com/default.aspx scid=kb;en-us;Q315159
    Check that you are able to use TCP/IP connections instead
  2. Create a test file on your PC named "connection.udl" - it's just an empty text file. Run this file, and set the configuration parameters inside it to create a connection string. Perform a test connection after you have finished entering database details and connnection parameters. When you close the dialog, the udl file will now contain a connection string. Remove the "provider=" parameter, and you can then use it with a SqlConnection's ConnectionString parameter.






Re: SQL Server 2005 Remote Connection Problem... Big Time.

xFors

Thanks for the reply. I installed it as named instance and I have made sure that my connection string was like "data source=<RemoteServerName>\<InstanceName>" and that sql browser is always running. Still the same error.



Re: SQL Server 2005 Remote Connection Problem... Big Time.

xFors

Thanks,

I am using TCP/IP connections.

Unfortunately I still can't connect to my SQL Server remotely using Management Studio.





Re: SQL Server 2005 Remote Connection Problem... Big Time.

??r?s而考老h旨r Sc?汛l而缶灰

Did you try the "connection.udl" method (point 2) Are you able to connect when you set connection options using this method





Re: SQL Server 2005 Remote Connection Problem... Big Time.

xFors

yes I certainly did. here is what's in it:

[oledb]
; Everything after this line is an OLE DB initstring
Provider=SQLNCLI.1;Persist Security Info=False;User ID=sa;Data Source=[SERVER]\[SQLSERVERINSTANCENAME]

it did not work





Re: SQL Server 2005 Remote Connection Problem... Big Time.

??r?s而考老h旨r Sc?汛l而缶灰

  1. Are you able to connect to your SQL Express Server from the machine on which its installed (eg. using the UDL file method). If so, please include the connection string that you've used that worked.
  2. If so, are you able to connect using Management studio installed on that local machine If not, then this needs to be corrected first.
  3. Once you are able to connect from the local machine, then try from a remote machine. What are you using to connect Is it a .NET program If so, what provider are you using (SQL Server Native, ODBC... )
  4. Have you checked the SQL Server Event log Is there anything there indicating that the logon was denied for security reasons
  5. When you attempt to navigate to the server in a connection dialog (eg. using the UDL method, or from within Management Studio), do you see your server machine's name appear

Please describe the steps you have taken in more detail. Perhaps your can refer to the machines as SERVERMACHINE and REMOTEMACHINE for clarity.

Also have a look at the following thread:
http://forums.microsoft.com/MSDN/ShowPost.aspx PostID=298847&SiteID=1





Re: SQL Server 2005 Remote Connection Problem... Big Time.

xFors

1- Yes, absolutely. I am definetely able to connect to SQL Server (Not Express) from the SERVERMACHINE on which it is installed. Using both UDL method and Management Studio.

2- Again, I am able to connect to SQL Server on SERVERMACHINE using Management studio.

3- I have been triyng to connect from the REMOTEMACHINE. I have registered the SERVERMACHINE in Management Sutudio in REMOTEMACHINE and tried to connect. I have also tried to connect from the SERVERMACHINE using UDL method.

4- Could not see anything in the event log in SERVERMACHINE indicating that the logon was denied.

5- Yes, the SERVERMACHINE's name appear both in UDL and within the management Studio in SERVERMACHINE.

This is the UDL connection script...

[oledb]
; Everything after this line is an OLE DB initstring
Provider=SQLNCLI.1;Password=[PASSWORD];Persist Security Info=True;User ID=sa;Data Source=SERVERMACHINE\INSTANCENAME

When I use this script in SERVERMACHINE to connect to SQL server in SERVERMACHINE, It DOES connect with no problems.

It is only when I try it in REMOTEMACHINE to connect to SQL server in SERVERMACHINE then it does not connect.

This whole thing started when I uninstalled the SQL Server Express Edition on the SERVERMACHINE and installed SQL Server (180 days Full Trial Edition) on the SERVERMACHINE. Up until then, I could connect both locally and more importantly remotely, using management studio with no problems. I have not altered any settings in the SERVERMACHINE that could block any remote connection requests, as a matter of fact when I try to connect, I disable the firewall and unassign all IP filters just to make sure.





Re: SQL Server 2005 Remote Connection Problem... Big Time.

??r?s而考老h旨r Sc?汛l而缶灰

So neither method works when trying to connect to SQL Server 2005 (correct or 2000 ) from REMOTEMACHINE to SERVERMACHINE

  1. Please confirm that you are using SQL Server 2005 on SERVERMACHINE .
  2. Ensure network connectivity - can you do a ping from REMOTEMACHINE to SERVERMACHINE, or, are you able to connect using Windows Explorer from REMOTEMACHINE to SERVERMACHINE, browsing a folder for instance (eg. \\SERVERMACHINE\c$\temp from REMOTEMACHINE)
  3. Ensure that you have set up SQL Server on SERVERMACHINE to allow remote connections from other computers. On SERVERMACHINE:
    1. Run: Start Menu > Programs > Microsoft SQL Server 2005 > Configuration Tools > SQL Server Surface Area Configuration
    2. At the bottom of the window, choose "Surface Area Configuration for Services and Connections"
    3. On the left, select MSSQLSERVER \ Database Engine \ Remote Connections
    4. Ensure "Local and Remote Connections" for the Client protocol that you wish to use. I will continue to articulate for "TCP/IP"
    5. On the left, under "SQL Server Browser" \ Service, ensure that the service is running, and set to startup option "Automatic"
    6. (I assume the SQL Server Service is running correctly, as you are able to connect from the server machine, SERVERMACHINE)
  4. Ensure the server machine is set up correctly to accept TCP/IP connections. On SERVERMACHINE:
    1. Run: Start Menu > Programs > Microsoft SQL Server 2005 > Configuration Tools > SQL Server Configuration Manager
    2. In the MMC console window, select "SQL Server Configuration Manager (Local)" > SQL Server 2005 Network Configuration > Protocols for MSSQLSERVER
    3. The right-hand pane lists configured protocols that the server will use to accept connections. Ensure that "TCP/IP" is Enabled (or Named Pipes if you wish to use Named Pipes). To enable:
      1. Double-click TCP/IP
      2. On the protocol tab, ensure Enabled is set to "Yes"
      3. On the "IP Addresses" tab, ensure that "Active" is "Yes" and "Enabled" is "Yes" for the IP address that you wish to connect via. I would suggest enabling ALL addresses at this stage, to eliminate problems, then disabling unwanted IP addresses LATER after connectivity can be established.
      4. Save changes (OK), and close the dialog.
  5. Now to ensure that your client is set up to use the appropriate client protocol that the server will accept. On REMOTEMACHINE:
    1. Run: Start Menu > Programs > Microsoft SQL Server 2005 > Configuration Tools > SQL Server Configuration Manager
    2. In the MMC console window, select "SQL Server Configuration Manager (Local)" > SQL Native Client Configuration > Client Protocols
    3. Ensure that you use a client protocol that was configured in the previous step that the server on SERVERMACHINE will accept. I used "TCP/IP".
    4. In the pane on the right, select TCP/IP, and ensure it is enabled. If you double-click on it, it will allow you to set "Enabled" to Yes. Note that the default port is 1433. This is for the default instance, if you have a named instance, it is more than likely that it is running on a different port. Leave value 1433 for now, as this value affects all outgoing connections.
  6. Now try to connect on REMOTEMACHINE using your connection string.
  7. If it does not work, then perhaps you'll have to modify your connection string to be more explicit about your intentions. Firstly, get the IP address, and instance listening port on the server. On SERVERMACHINE:
    1. Using SQL Management Studio, connect to the database, and execute the following query:

      Use master
      Go
      Xp_readerrorlog


    2. This will give you a heap of information, but the line you're looking for is that below:
      2006-03-24 14:47:38.290 Server Server is listening on [ ***.***.***.*** <ipv4> 99999].
    3. Use the IP address (in green), and the Instance Listen-On port number (purple) to compose your connection string as follows:
      ;Persist Security Info=True;SERVER=SERVERMACHINE;;DATABASE=master;Network=DBMSSOCN;Address=
      ***.***.***.***,99999;UID=sa;PWD=[PASSWORD];
    4. Note that I haven't included the "Provider=SQLNCLI.1;". This connection string can now be used from a .NET program.
  8. Using the above connection string, attempt to connect to to your database using your sa account and password (change security settings to be more strict after establishing connectivity).






Re: SQL Server 2005 Remote Connection Problem... Big Time.

??r?s而考老h旨r Sc?汛l而缶灰

xFors - is your problem now resolved




Re: SQL Server 2005 Remote Connection Problem... Big Time.

xFors

I had done everything except number 7. When I ran that query I saw these lines:

Server is listening on [ 'any' <ipv4> 4438].
Server local connection provider is ready to accept connection on [ \\.\pipe\SQLLocal\[instancename] ].
Server local connection provider is ready to accept connection on [
\\.\pipe\MSSQL$[instancename]\sql\query ].
Server is listening on [ 'any' <ipv4> 1141].
Dedicated admin connection support was established for listening remotely on port 1141.
The SQL Network Interface library could not register the Service Principal Name (SPN) for the SQL Server service. Error: 0x54b. Failure to register an SPN may cause integrated authentication to fall back to NTLM instead of Kerberos. This is an informational message. Further action is only required if Kerberos authentication is required by authentication policies.
SQL Server is now ready for client connections. This is an informational message; no user action is required.


Instead of an ip address it said 'any' and I don't know if it is something I should worry about.

Anyway I have attached ',4438' at the end of SERVERMACHINE in Management Studio in REMOTEMACHINE and it worked.

4438 is the port specified in tcp/ip Properties > IpAll > Tcp Dynamic Ports in SERVERMACHINE

in tcp/ip Properties in SERVERMACHINE:

IP1
active, enabled
tcp dynamic ports = 0
tcp port = blank
IP2
active, enabled
tcp dynamic ports = 0
tcp port = blank
IpAll
tcp dynamic ports = 4438
tcp port = blank

1-Are there any adjustments I should make to this settings in order not have to add 4438 at the end of SERVERMACHINE in my connection strings

2-If not does this mean that I have to change this port number in my connection string everytime the sql server is restarted or the port number dynamicaly changed

Thanks





Re: SQL Server 2005 Remote Connection Problem... Big Time.

Peter Gvozdjak - MSFT

Yes, the SQL Browser service allows you to connect to the SQL Server without specifying the port, simply by specifying SERVERMACHINE\INSTANCENAME.

To have this work you must ensure that:

(a) The SQL Browser service is running on the remote SQL Server machine.

(b) UDP port 1434 is not blocked by firewall. If you use Windows firewall you can grant an exception either to port UDP 1434 directly, or to the SQL Browser process (sqlbrowser.exe).

Let me know whether this helps solve your problem.