locked
Connecting to SQL Server without instance name RRS feed

  • Question

  • This is probably something basic I'm overlooking, but... I have a 2017 database engine on a named instance with non-default port.  So connection string should be ServerName\InstanceName, xxxx.  I can connect from a remote Management Studio without providing instanceName - just ServerName, xxxx.  I noticed SQL Browser was running, so I disabled it.  however, I can still connect without InstanceName.  How do I ensure InstanceName is required in the connection?  Thanks.
    Saturday, January 12, 2019 6:20 AM

Answers

  • "servername,port" is always possible to use (unless you disable TCP/IP and go for named pipes only). If you say "server/instance,port", the instance name is flatly ignored.

    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    • Marked as answer by District9 Saturday, January 12, 2019 4:50 PM
    Saturday, January 12, 2019 9:27 AM
  • I can connect from a remote Management Studio without providing instanceName - just ServerName, xxxx.  I noticed SQL Browser was running, so I disabled it.  however, I can still connect without InstanceName.  How do I ensure InstanceName is required in the connection?  Thanks.

    You cannot require clients to specify an instance name. If the client (including SSMS) specifies a port number or the instance is listening on the default 1433 port, the client will be able to connect without an instance name. The SQL Server Browser service is involved only when an instance name is specified and a port number is omitted.

    I think you want to omit the port number and leave the SQL Server Browser service running. This will allow clients to connect using servername\instancename, but not require it.



    Dan Guzman, Data Platform MVP, http://www.dbdelta.com

    • Marked as answer by District9 Saturday, January 12, 2019 4:50 PM
    Saturday, January 12, 2019 12:31 PM

All replies

  • Are you sure there was no default instance on your server?

    I guess there was also a default instance in the server which is what you got connected to when you just specified ServerName alone


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Saturday, January 12, 2019 7:27 AM
  • "servername,port" is always possible to use (unless you disable TCP/IP and go for named pipes only). If you say "server/instance,port", the instance name is flatly ignored.

    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    • Marked as answer by District9 Saturday, January 12, 2019 4:50 PM
    Saturday, January 12, 2019 9:27 AM
  • I can connect from a remote Management Studio without providing instanceName - just ServerName, xxxx.  I noticed SQL Browser was running, so I disabled it.  however, I can still connect without InstanceName.  How do I ensure InstanceName is required in the connection?  Thanks.

    You cannot require clients to specify an instance name. If the client (including SSMS) specifies a port number or the instance is listening on the default 1433 port, the client will be able to connect without an instance name. The SQL Server Browser service is involved only when an instance name is specified and a port number is omitted.

    I think you want to omit the port number and leave the SQL Server Browser service running. This will allow clients to connect using servername\instancename, but not require it.



    Dan Guzman, Data Platform MVP, http://www.dbdelta.com

    • Marked as answer by District9 Saturday, January 12, 2019 4:50 PM
    Saturday, January 12, 2019 12:31 PM
  • As mentioned, [servername],[port] is enough to connect to any instance (assuming port is correct) even though [servername]\[instancename],[port] will work, as will [servername] (the latter assuming you are running on the default port number).

    [servername]\[instance] is only needed when:

    1. You are not specifying the port number

    and

    2. You are not running on the default port

    You cannot stop people from specifying instance if they want to through SQL, but you could using Firewall packet filtering (so that you can force what connection strings are allowed through).

    Explicitly using port number for connections can cause confusion when availability groups (with listener) are used since developers are sometimes not clear what port to use (the answer is the listener port), but this is purely an educational issue so I do not have any opinion whether using port number or not is good or bad practice<g class="gr_ gr_44 gr-alert gr_gramm gr_inline_cards gr_run_anim Style replaceWithoutSep" data-gr-id="44" id="44">.</g>


    <g class="gr_ gr_44 gr-alert gr_gramm gr_inline_cards gr_disable_anim_appear Style replaceWithoutSep" data-gr-id="44" id="44">Regards</g>,
    Mark Broadbent.
    Microsoft Certified Master | Microsoft Data Platform MVP


    Please click "Propose as answer" if a post solves your problem
    or/and vote the post up if <g class="gr_ gr_58 gr-alert gr_gramm gr_inline_cards gr_run_anim Grammar multiReplace" data-gr-id="58" id="58"><g class="gr_ gr_59 gr-alert gr_spell gr_inline_cards gr_run_anim ContextualSpelling multiReplace" data-gr-id="59" id="59">its</g></g> been helpful.

    Interested in attending a free training? Why not attend SQLSaturday Cambridge and SharePoint Saturday Cambridge? And why not check out my Ultimate Training Resources Page?



    • Edited by retracementMVP Monday, January 14, 2019 9:19 AM again html formatting is a problem
    Monday, January 14, 2019 9:15 AM