Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
SSMS not seeing all instances on SQL 2008

Answered SSMS not seeing all instances on SQL 2008

  • Thursday, October 04, 2012 12:08 AM
     
     
    Sign In to Vote
    0

    From my Windows 2003 Server running SQL 2005 using SSMS I try to connect to my Windows 2008 Server running SQL 2008 I can see the main instance MySQLServer but I have two additional instances running server10/test1 server10/production

    When I run SSMS on the 2008 Server I can see all the instances.

    I have a Web Server on Windows 2003 server that uses a connect.db command and all my asp code uses that connect to point to the sql server.

    I moved the data base from SQL 2005 to the SQL 2008 server.

     But now noone can access the data from the web site.

    I think If I can see the server10/production instance on SSMS from the same server that is running the web site Ithe data will start to be seen.

    Any ideas on why I can not see the other instances from another computer?

    Any command line test from a workstation or server I can run to test this?

    Thanks

    Tom

    Thomas R Grassi Jr

     

All Replies

  • Thursday, October 04, 2012 1:01 AM
     
     Answered
    Sign In to Vote
    0

    Hi,

    Have you enabled a firewall rule to allow the server10\test1 and server10\production TCP ports to be accessible over the network from your web server? Named instances will use dynamic TCP ports by default these ports won't be allowed externally by the Windows firewall. I usually set a static port and enable the rules required.

    Sean Massey | Consultant, iUNITE

    Feel free to contact me through My Blog, Twitter or Hire Me.
    Please click the Mark as Answer or Vote As Helpful button if a post solves your problem or is helpful!

    • Marked As Answer by TRGOneCare Thursday, October 04, 2012 12:55 PM
    •  
     
  • Thursday, October 04, 2012 1:13 AM
     
     Answered
    Sign In to Vote
    0

    Sean

    Thanks for the fast responce.

    I just added

    netsh advfirewall firewall add rule name = SQLUDP dir = in protocol = udp action = allow localport = 1434 remoteip = localsubnet profile = DOMAIN

    I had already

    netsh advfirewall firewall add rule name = SQLPort dir = in protocol = tcp action = allow localport = 1433 remoteip = localsubnet profile = DOMAIN

    Getting this from the web page

    Microsoft OLE DB Provider for SQL Server

    error
    '80004005'

    [DBNETLIB][ConnectionOpen (Connect()).]SQL Server
    does not exist or access denied.

    After I added the UPD 1434 to the firewall I am now able to see the other instances from my windows 7 computer runnng SMSS or the Windows 2003 Server

    But I can not open the instance i get this

    TITLE: Connect to Server
    ------------------------------

    Cannot connect to SERVER10\Production.

    ------------------------------
    ADDITIONAL INFORMATION:

    A network-related or instance-specific error occurred 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: TCP Provider, error: 0 - A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond.) (Microsoft SQL Server, Error: 10060)

    For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=10060&LinkId=20476

    ------------------------------
    BUTTONS:

    OK
    ------------------------------

    How to I configure sql server 2008 to allow remote connections?

    Hope this helps

    Tom 

    Thomas R Grassi Jr

    • Marked As Answer by TRGOneCare Thursday, October 04, 2012 12:56 PM
    •  
     
  • Thursday, October 04, 2012 1:20 AM
     
     Answered
    Sign In to Vote
    0
    Open up SQL Server Configuration Manger > SQL Server Network Configuration > Protocols for Test1 > Double-click TCP/IP > IP Addresses > scroll to the botton and look for your TCP port. Add that to the firewall rules. You should repeat the same for Production.

    Sean Massey | Consultant, iUNITE

    Feel free to contact me through My Blog, Twitter or Hire Me.
    Please click the Mark as Answer or Vote As Helpful button if a post solves your problem or is helpful!

    • Marked As Answer by TRGOneCare Thursday, October 04, 2012 12:56 PM
    •  
     
  • Thursday, October 04, 2012 1:21 AM
    Moderator
     
     Answered
    Sign In to Vote
    0

    Would you please check-out

    Steps to troubleshoot SQL connectivity issues

    Regards,
    Ahmed Ibrahim
    SQL Server Setup Team
    My Blog
    This posting is provided "AS IS" with no warranties, and confers no rights. Please remember to click "Mark as Answer" and "Vote as Helpful" on posts that help you.
    This can be beneficial to other community members reading the thread.
    View Ahmed Ibrahim's profile on LinkedIn

    • Marked As Answer by TRGOneCare Thursday, October 04, 2012 12:55 PM
    •  
     
  • Thursday, October 04, 2012 1:39 AM
     
     Answered
    Sign In to Vote
    0

    Sean

    Thanks

    I added port 1433 that did not work after I restarted the instance.

    Should I use another port number? do you have any recommendations on what to use?

    Thanks

    Thomas R Grassi Jr

    • Marked As Answer by TRGOneCare Thursday, October 04, 2012 12:56 PM
    •  
     
  • Thursday, October 04, 2012 1:46 AM
     
     Answered
    Sign In to Vote
    0

    From the sounds of it you have a single SQL 2008 server that has the following instances: -

    MSSQLSERVER

    TEST1

    Production

    If this is accurate then only one of the instances can be using port 1433 at any one time on that server. Therefore I would believe that the MSSQLSERVER is using port 1433. The other two instances will use a dynamic TCP port and you need to look in SQL Server Configuration Manager to find out what port those instances are using.

    You can also find the TCP port information in the registry but its a pain. Did the steps I posted above make sense?

    Sean Massey | Consultant, iUNITE

    Feel free to contact me through My Blog, Twitter or Hire Me.
    Please click the Mark as Answer or Vote As Helpful button if a post solves your problem or is helpful!

    • Marked As Answer by TRGOneCare Thursday, October 04, 2012 12:56 PM
    •  
     
  • Thursday, October 04, 2012 1:48 AM
    Moderator
     
     Answered
    Sign In to Vote
    0

    Hello,
    Additionally, please see the following resources:

    http://blogs.msdn.com/b/spike/archive/2008/11/07/sql-server-error-10060-sql-server-error-10061.aspx

    “On Server side:

       1) Did you enable FW on your server which has SQL Server installed? If so, did you open sql port in the exception list? and did you add sqlbrowser.exe to the exception list?

       2) Did your SQL Server started successfully? Can you double check the server ERRORLOG? or use "sc query mssql$sqlexpress", what you got? Remember, by default sql express is a named instance, and you need to enable tcp or np on the server side to make remote connection, also, you can get tcp port from errorlog that sqlexpress was listening on, try "telnet <remoteserver> <port>" on your server box, see whether it works?

    Client side:

    3) You said, by using "osql or sqlcmd" connection works, so how does your connection string in your client application look like? Can you enlarge connection timeout?

    Finally, are your client and server in the same domain?

    Check out following sqlexpress blog about best practice to connec to sqlexpress and blog about troubleshooting list for sql remote connection.

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

    http://blogs.msdn.com/sql_protocols/archive/2006/03/23/558651.aspx”
    http://blogs.msdn.com/b/sql_protocols/archive/2005/10/22/sql-server-2005-connectivity-issue-troubleshoot-part-i.aspx?PageIndex=3

    Hope this helps.

    Regards,
    Alberto Morillo
    SQLCoffee.com


     
  • Thursday, October 04, 2012 1:56 AM
     
     Answered
    Sign In to Vote
    0

    Sean

    Yes they do but when I went into SSCM the ports were blank nothing listed at all I just added 1433 to them

    So if they are dynamic I need to pick some? Then once I define those ports add them tcp and udp to my firewall exceptions?

    Since they are blank what should I chose for the port?

    Thomas R Grassi Jr

    • Marked As Answer by TRGOneCare Thursday, October 04, 2012 12:56 PM
    •  
     
  • Thursday, October 04, 2012 2:08 AM
     
     Answered
    Sign In to Vote
    0

    Only one process can listen on any individual port at once. Therefore you can't set port 1433 for all your instances unless only one of the instances will be running at any one time.

    What was the TCP Dynamic Ports value set to when you check in SSCM?

    Here are the steps to set a SQL to listen on a specific port - http://msdn.microsoft.com/en-us/library/ms177440(v=sql.105).aspx

    In my environments I don't leave SQL running on the default or using dynamic ports. I change the port to a dedicated port so that I can document it and I know which port each instance is available on. I can then have firewall rules enabled for that specific port/IP address combination.

    Sean Massey | Consultant, iUNITE

    Feel free to contact me through My Blog, Twitter or Hire Me.
    Please click the Mark as Answer or Vote As Helpful button if a post solves your problem or is helpful!

    • Marked As Answer by TRGOneCare Thursday, October 04, 2012 12:56 PM
    •  
     
  • Thursday, October 04, 2012 2:17 AM
     
     Answered
    Sign In to Vote
    0

    Sean

    on production it was 49477

    on test it was 50135

    The trick is which port to use 

    Anyknow how to make the change but not sure which port to use.

    Thomas R Grassi Jr

    • Marked As Answer by TRGOneCare Thursday, October 04, 2012 12:56 PM
    •  
     
  • Thursday, October 04, 2012 2:46 AM
     
     Answered
    Sign In to Vote
    0

    It doesn't really matter as long as nothing else is using those ports on the server. If you change the port config you need to restart SQL server.

    If you wish to keep the ports static and therefore change them from dynamic why not use the dynamic ports that are being used now but just make them static?

    If you allow these ports on the firewall can you now connect from your web app?

    Sean Massey | Consultant, iUNITE

    Feel free to contact me through My Blog, Twitter or Hire Me.
    Please click the Mark as Answer or Vote As Helpful button if a post solves your problem or is helpful!

    • Marked As Answer by TRGOneCare Thursday, October 04, 2012 12:56 PM
    •  
     
  • Thursday, October 04, 2012 12:55 PM
     
     Answered
    Sign In to Vote
    0

    Sean

    I used the dynamic ports

    changed the dynamic ports to static and added those to the firewall exception list for both udp and tcp

    All working now.

    Thanks

    Thomas R Grassi Jr

    • Marked As Answer by TRGOneCare Thursday, October 04, 2012 12:56 PM
    •