FreeTDS Development Group

["James K. Lowden" <jklowden AT freetds.org>]

Duncan Kerr wrote:
> If I connect using port in my .conf file, I can ask
> the sqlserver what it's instance name is, but if I then try and connect
> using that instance name it fails, ie
> 
> [mcdb]
>     host = ntdevmog451
>     tds version=8.0
>     port=1433
> 
> lxdevws85 apps% tsql -S mcdb -U FUENV4\\fuadmin -P ful1ffe
> locale is "C"
> locale charset is "ANSI_X3.4-1968"
> 1> select @@ServerName
> 2> go
> 
> NTDEVMOG451
> (1 row affected)

Try this query instead:

        select serverproperty('InstanceName')

It returns NULL for the default instance (or if there's only one, I'm not
sure).  That sort of makes sense,  because that's what's returned if the
argument is invalid, and on earlier versions of the server that didn't
support instances, 'InstanceName' was invalid.  

> I still don't understand the distinction between port and instance

Much as DNS assigns a name to the machine's IP address, SQL Server assigns
a name to the port served by the instance.  Unix is full of this sort of
thing, of course; for example, a directory assigns a name to an inode.  

Microsoft could have used/adapted the DNS protocol for its instance:port
map, but chose to invent something either simpler or inferior, depending
on how you look at it.  The client queries the server at well known port,
always 1434, with a tiny UDP message.  The client sends the instance name,
and the server returns the associated port number, if any.  

Recent snapshots provide a clumsy way to solicit instance information from
the server using tsql:

$ ~/freetds/build/src/apps/tsql -H $S -U$U -P$P -L 
locale is "C"
locale charset is "646"
     ServerName MS03NJS0878
   InstanceName MSSQLSERVER
    IsClustered No
        Version 8.00.194
            tcp 2500
             np \\MS03NJS0878\pipe\sql\query
Missing argument -p, looking for default instance ... 
        found default instance, port 2500
1>

The -L argument causes tsql to list all instances on a given host (note:
not server.  A server is a host-port-protocol tuple).  tsql queries port
1434 for information on all instances.  The information is printed
verbatim, apart from being formatted for human consumption.  On the above
server there's only one.  Because no -p argument was provided, tsql looked
for the default instance, found it, and connected.  Whoo hoo!  

Now perhaps it's clear why a freetds.conf entry should have either
instance or port, but not both.  In the event the user includes both, the
system must choose: which do you mean?  The freetds.conf man page mentions
that instance supersedes port.  That's probably not the best choice,
though (and might change one day), because port is not subject to
interpretation, whereas instance requires the server to provide the port
number.  

A secondary weakness is that if the instance name is incorrect, the server
doesn't answer.  There's no error message.  The user is told the
connection failed, but not precisely why.  

I'm working, slowly, on sorting all this out, such that connection
failures and name-lookup failures and login-protocol failures and
login-authentication failures are all distinct.  I hope this little
discussion helps explain why I think users need them to be distinct: if
the messages are clear, it's easier to know what to fix.  

Regards, 

--jkl