sqlcmd with trusted connection and IP address or fully qualified hostname

sqlcmd with trusted connection and IP address or fully qualified hostname

• S10n

  This should not happen. Most likely, you have a DNS issue. Can you make sure your IP address, hostname and FQDN are mapped to each other correctly (using "ping -a" and/or nslookup)
  
  

• veXed

  Hi, Georges

  1) Is your machine really in a domain(Is your machine in a WORKGROUP) Are you sure when you use:

  ping <machinename> return ip address and

  ping ipaddress return <FQDN>

  2) which account sqlcmd running under Is it a domain account or a local machine account

  3) Could you double check: a. computer -> manage -> local user and groups, whether there are invalid domain account left remove them, rejoin your computer to the domain, and see what happens

  If your machine is in a WORKGROUP, and you fail to connect over IP address and FQDN, that is a OS known issue. The workaround exists.

  Thanks!

  Ming.

  
  

• Rosh K Mathews

  I am not sure which SQL login group you are talking about. I cannot see the group in the list of OS groups, and the SQL Server Logins already has the BUILDIN\Administrators of which the Administrator is a member.

  I would appreciate if you could explain how to add the machine account (Administrator I presume) to SQL login group.

  Thanks.

  Georges

  
  

• John.Doe

  This is an interesting issue. When you use IP or FQDN, the machine think you are a remote connection. SQL does not allow your machine's account to login. If using 127.0.0.1 or hostname, the machine think the connection is local and SQL grants it the login. You can add the machine account to SQL login group to enable remote logon of your "local machine".
  

• Dawid &amp;#321;azi&amp;#324;ski

  This workaround doesn't work for me because I have only one machine on which SQL Server is installed and the sqlcmd command is running, and that one machine is not in the domain.
  
  For a while, I wasn't able to reproduce the problem until it came back on some machines and not others. The differences is what Ming suspected. It works on machines in the domain, and it fails on machines in a workgroup.
  
  I believe that what I really need is the workaround that exists for the known issue if the machine is in a WORKGROUP, and sqlcmd fails to connect over IP address and FQDN.
  
  Thanks.
  
  Georges
  
  
  

• Katherine

  Did you use "ipconfig /flushdns" to flush DNS cache Please enter your ip/hostname/fqdn at least twice consecutively for each If a DNS have an issue, usually we see different results from time to time. If that's not the case, then we can look at further option. Thanks.
  
  

• drcairo

  1) There are now two independent machines where I can reproduce this, and they are both in different workgroup.

  For both machines, "ping %computername%" returns: "Pinging %computername% [nnn.nnn.nnn.nnn] with 32 bytes of data" while "ping nnn.nnn.nnn.nnn" returns "Pinging nnn.nnn.nnn.nnn with 32 bytes of data".

  For both machines, "nslookup %computername%", "nslookup %computername%.domain", and "nslookup nnn.nnn.nnn.nnn" all return the same: "Name: %computername%.domain, Address: nnn.nnn.nnn.nnn", even after the "ipconfig /flushdns".

  2) On both machine, the sqlcmd is running under the Administrator account (the local account)

  3) Those machine are is a workgroup, and there are no invalid domain account.

  I guess that my problem is the known issue you are talking about. What is the workaround

  Thanks.

  Georges

  
  

• Kamii47

  I did the "ipconfig /flushdns" twice, rerun my test twice and got the same problem. I can even reproduce the same problems on another clean machine with a silent and minimal install of SQL Server 2005. Those machines are configured with DHCP and are on our intranet.

  Georges

  
  

• balusmca

  Assuming your machine is on domain. You can try

  sp_grantlogin @loginame="<domainname>\<machinename>$"

  sp_grantdbaccess @loginame='<domainname>\<machinename>$',@name_in_db='<machinename>'

  sp_addrolemember @rolename='db_owner',@membername='<machinename>'

  HTH

  
  

• shibin

  Both "ping -a" and nslookup refer to the same IP address for hostname, hostname.domain and real IP address. I also tried further tests and here are the results, where hostname is the hostname without the domain, hostname.domain is the fully qualified hostname including domain, realIP is the real IP address of the machine, and localIP is 127.0.0.1:

  OK: sqlcmd -E -S hostname -Q quit

  Error: sqlcmd -E -S hostname.domain -Q quit

  Error: sqlcmd -E -S realIP -Q quit

  OK: sqlcmd -E -S localIP -Q quit

  OK: sqlcmd -E -S tcp:hostname,1433 -Q quit

  Error: sqlcmd -E -S tcp:hostname.domain,1433 -Q quit

  Error: sqlcmd -E -S tcp:realIP,1433 -Q quit

  OK: sqlcmd -E -S tcp:localIP,1433 -Q quit

  Anything else I can test

  Thanks.

  Georges

  
  

• Alex Foygel

  For workgroup, you need to create a machine user account on both machine with same user name and password. Grand the user account access to your sql server and run your sqlcmd under that user account.

  HTH

  
  
• sqlcmd with trusted connection and IP address or fully qualified hostname