locked
SQL Server 2008 NOV CTP: Remote connections does not allow login to query tables RRS feed

  • Question

  • SQL Server 2008 NOV CTP: Remote connections does not allow login to query tables

     

    I installed an instance of 2008 on the same server as a 2005. The 2005 insatnce is working fine. Remote connections allow queries of the data.  The  2008 instance doesn't not, but I can do queries directly on the server via odbc and sql studio 2008 (sql clinet10) connections. I have both remote access" and "remote adminstration" turned on on the 2008 instance, but can only see the schema and table . When I try to query the data, I get a hanging exceuting prompt with out timeout or error. Has anyone else seen this? Is remote connection not allow with the CTP version?

    Friday, January 11, 2008 6:22 PM

Answers

  • http://blogs.msdn.com/sql_protocols/archive/2006/09/30/SQL-Server-2005-Remote-Connectivity-Issue-TroubleShooting.aspx

     

    This all applies to SQL Server 2008.

     

    III. Troubleshooting Tips:

    Assumption: your SQL Server was installed on remote server and behind firewall; SQL Instance was started; and  your client app specify correct remote sql instance name.

    On your server side:

    [1] Enable remote named pipe or tcp: All programs | Microsoft SQL Server 2005| Configuration Tools | SQL Server Surface Area Configuration | Configuration for Services and Connections | Remote Connections, choose either enable TCP or Named Pipe or both.

    [2] Sql Instance was restarted successfully, check Server ErrorLog, find which tcp port or pipe name server is listening on.

    [3] netstate -ano | findstr <portnumber> if server enable TCP, and make sure server is listening on the correct port.

    [4] go to services.msc, find service "SQL Server Browser", enable it and restarted, also, go to SQL Server Configuration Manager, check properties for SQL Browser service, in Advanced tab, make sure it is active.

    [5] Enable "Fire and Printer Sharing" in Firewall exception list.

    Devil Add TCP port or sqlservr.exe to Firewall exception list, either add "..\Binn\sqlsevr.exe" or add port.

    If your server was not started successfully by any reason, it is very helpful to collect info from server logs; also, you can get clear picture of protocols that server is listening on, for eg, if TCP was enabled, you should be able to see which port server is listening on, and if Np was enabled, you can make connection throgh the pipe name.

    [7] Add Sql Browser service to Firewall exception list, you can either add program " C:\Program Files\Microsoft SQL Server\90\Shared\sqlbrowser.exe" or add UDP port 1434.

    Music Make sure if your remote sql Server Instance is a default instance, it must listen on tcp port 1433 and pipe \\.\pipe\sql\query.

    On your Client Side:

    [1] ping <remote server> return correct IP address of your remote server.

    [2] telnet <remoteserver> <portnumber> works, <portnumber> is the port that your remote sql instance is listening on.

         telnet < ipaddress> <port> works

    [3] \\<remoteserver>, make sure you can access share of remote server.

    [4] Go to SQL Server Configuration Manager | SQL Native Client Configuration | Client Protocols, make sure Named pipe or tcp is enabled; Click properties of Client Protocols, make Sure you see at least Np or tcp is in enabled protocols, plus, recommend put TCP on the top of the order.

    [5] If you are using MDAC ( Driver={SQL Server} or SQLOLEDB.x) in your client application, in command line, tyep "cliconfg.exe", also enable NP and TCP and put TCP on top of order.

    Devil Use osql or sqlcmd to try " osql /S<remoteserver> /E" or "osql /S<remoteserver>\<Instancename> /E" see whether connection works.

    [7] Check your client connection string syntax: a. do you specify correct remote server name? b. do you specify correct instance name? if remote sql is default instance, you just need to specify remote server machine name, otherwise, you need to specify " <remoteserver>\<instancename>" c. do you  spcify tcp port or pipe name in your connection string, if so, double check the port and pipe name are the one server is listening on, otherwise, remove it.

    Music If your client app connect to remote server using server alias name, a. if you are using MDAC, try "cliconfg.exe", in "Alias" Tab, check whether you specify any alias in which the tcp port or the pipe name should be the one remote server is listening on; b. if you are using SQL Native Client, go to SQL Server Configuration Manager, check "Aliases" configuration.

    If you are sure the network protocol configuration for remote connection are all correct by going through above checklist, and still face specific problem, pleas refer following blog that give troubleshooting tips based on concrete error message:

    Thursday, January 31, 2008 8:20 PM

All replies

  • I have had the same problem and I thought it is because SQL Server 2008 is just a CTP... but it's not. Please check the firewall from your server. I disabled the firewall and it worked for me.

     

    If after disabling the firewall you'll still be unable to connect remotely to your sql server 2008 instance, please check the SQL Server browser service which must be running.

     

    Best,

    Radu.

     

    Monday, January 14, 2008 11:39 AM
  • http://blogs.msdn.com/sql_protocols/archive/2006/09/30/SQL-Server-2005-Remote-Connectivity-Issue-TroubleShooting.aspx

     

    This all applies to SQL Server 2008.

     

    III. Troubleshooting Tips:

    Assumption: your SQL Server was installed on remote server and behind firewall; SQL Instance was started; and  your client app specify correct remote sql instance name.

    On your server side:

    [1] Enable remote named pipe or tcp: All programs | Microsoft SQL Server 2005| Configuration Tools | SQL Server Surface Area Configuration | Configuration for Services and Connections | Remote Connections, choose either enable TCP or Named Pipe or both.

    [2] Sql Instance was restarted successfully, check Server ErrorLog, find which tcp port or pipe name server is listening on.

    [3] netstate -ano | findstr <portnumber> if server enable TCP, and make sure server is listening on the correct port.

    [4] go to services.msc, find service "SQL Server Browser", enable it and restarted, also, go to SQL Server Configuration Manager, check properties for SQL Browser service, in Advanced tab, make sure it is active.

    [5] Enable "Fire and Printer Sharing" in Firewall exception list.

    Devil Add TCP port or sqlservr.exe to Firewall exception list, either add "..\Binn\sqlsevr.exe" or add port.

    If your server was not started successfully by any reason, it is very helpful to collect info from server logs; also, you can get clear picture of protocols that server is listening on, for eg, if TCP was enabled, you should be able to see which port server is listening on, and if Np was enabled, you can make connection throgh the pipe name.

    [7] Add Sql Browser service to Firewall exception list, you can either add program " C:\Program Files\Microsoft SQL Server\90\Shared\sqlbrowser.exe" or add UDP port 1434.

    Music Make sure if your remote sql Server Instance is a default instance, it must listen on tcp port 1433 and pipe \\.\pipe\sql\query.

    On your Client Side:

    [1] ping <remote server> return correct IP address of your remote server.

    [2] telnet <remoteserver> <portnumber> works, <portnumber> is the port that your remote sql instance is listening on.

         telnet < ipaddress> <port> works

    [3] \\<remoteserver>, make sure you can access share of remote server.

    [4] Go to SQL Server Configuration Manager | SQL Native Client Configuration | Client Protocols, make sure Named pipe or tcp is enabled; Click properties of Client Protocols, make Sure you see at least Np or tcp is in enabled protocols, plus, recommend put TCP on the top of the order.

    [5] If you are using MDAC ( Driver={SQL Server} or SQLOLEDB.x) in your client application, in command line, tyep "cliconfg.exe", also enable NP and TCP and put TCP on top of order.

    Devil Use osql or sqlcmd to try " osql /S<remoteserver> /E" or "osql /S<remoteserver>\<Instancename> /E" see whether connection works.

    [7] Check your client connection string syntax: a. do you specify correct remote server name? b. do you specify correct instance name? if remote sql is default instance, you just need to specify remote server machine name, otherwise, you need to specify " <remoteserver>\<instancename>" c. do you  spcify tcp port or pipe name in your connection string, if so, double check the port and pipe name are the one server is listening on, otherwise, remove it.

    Music If your client app connect to remote server using server alias name, a. if you are using MDAC, try "cliconfg.exe", in "Alias" Tab, check whether you specify any alias in which the tcp port or the pipe name should be the one remote server is listening on; b. if you are using SQL Native Client, go to SQL Server Configuration Manager, check "Aliases" configuration.

    If you are sure the network protocol configuration for remote connection are all correct by going through above checklist, and still face specific problem, pleas refer following blog that give troubleshooting tips based on concrete error message:

    Thursday, January 31, 2008 8:20 PM
  • Hello

     

    The response from Debra Dove MSFT is on the right track, but is not completely correct for SQL Server 2008.

     

    Server Side Corrections:

    [1] Enabling remote connections in SQL Server 2008 through a Windows dialog is achieved through SQL Server Management Studio. Right click the server, and go to Facets.  Under the Server Configuration facet, "RemoteAccessEnabled" should be true.  You will need to restart the server.  (You may alternatively enable remote access other ways such as T-SQL, and you will still need to restart the server).  Depending on the access issue (or problem) you may need to also change other flags under Server Configuration or Surface Area Configuration (each in the drop-down menu).  The main point is to use SSMS and not look for "SQL Server Surface Area Configuration" under the "SQL Server 2008" folder because it is not there.

     

    [3] Instead of "netstate" it should be "netstat"

     

    [5] Instead of "Fire and Printer Sharing" it should be "File and Printer Sharing"  -- I don't want to share my fires

     

    A helpful tip: Debra mentions using osql and sqlcmd -- however, the preferred command line utility for SQL Server 2008 is only SQLCMD (osql is a legacy command that does not provide as full support for SQL Server 2008)

    See http://msdn.microsoft.com/en-us/library/ms165702.aspx

     

     

     

     

    Monday, December 1, 2008 8:59 PM
  •  MarkTab wrote:

    Hello

     

     

    Server Side Corrections:

    [1] Enabling remote connections in SQL Server 2008 through a Windows dialog is achieved through SQL Server Management Studio. Right click the server, and go to Facets.  Under the Server Configuration facet, "RemoteAccessEnabled" should be true.  You will need to restart the server.  (You may alternatively enable remote access other ways such as T-SQL, and you will still need to restart the server).  Depending on the access issue (or problem) you may need to also change other flags under Server Configuration or Surface Area Configuration (each in the drop-down menu).  The main point is to use SSMS and not look for "SQL Server Surface Area Configuration" under the "SQL Server 2008" folder because it is not there.




    Those facets don't exist either!?
    Tuesday, December 9, 2008 3:08 AM

  • >This all applies to SQL Server 2008.

    Cool, just the help I'm looking for!

    >
    [1] Enable remote named pipe or tcp: All programs |    Microsoft SQL Server 2005     |

    Hmm. That's odd. I don't have an entry for Microsoft SQL Server   2005!!!    on my SQL Server 2008!!! box.

    >
    SQL Server Surface Area Configuration

    S'funny, this tool apears not to exist in my SQL Server 2008 menu either.

    > So how are these instructions 'helpful' again?

    >This all applies to SQL Server 2008.

    No. No it does not. Not slightly, not even, not at all.

    Calling this helpful is like calling Exchange server 2007 stable, elegant and bug-free.

    And it fair drives me right up the freaking WALL!!!

    Good night.








    Thursday, January 21, 2010 7:01 AM

  • I can see them on mine;

    In the SQL Server Management Studion, right-click on the root, or first folder in the tree on the left hand side (on mine it's called servername\databaseinstance (SQL Server 10.0.2531 - servername\ Administrator). The "Facets" option is there.

    It'll open a "View Facets" window. Click in the drop-down arrow in the box at the top of the window to find "Server Configuration" , where the "RemoteAccessEnabled" entry is, and the "Surface Area Configuration" settings are in there too.




    Thursday, January 21, 2010 7:22 AM
  • check this out
    http://support.microsoft.com/kb/968872
    Monday, February 8, 2010 7:28 PM