SQL SERVER – Connecting to Server Using Windows Authentication by SQLCMD

Recently I got a call from an old friend I used to call “DJ”. Here is the exact conversation we had:

DJ: Pinal, your SQL Server does not like me.
Pinal: Possible, no one likes you!
DJ: I am serious…
Pinal: I am too…
DJ: No really – be serious.
Pinal: Well, you started it, tell me your problem.
DJ: This new SQLCMD thingy does not work. Everytime I am using my username and password it does not log me in.
Pinal: Have you tried with SSMS?
DJ: No, but it works fine. I just logged in my machine using it.
Pinal: Oh, are you trying to login using your Windows Authentication Username and Password?
DJ: Does it matter? I am the admin on my box.
Pinal: It does matter. Tell me your script.
DJ: sqlcmd -S .\sqlent1 -U username -P password. And then I’d been given “error 18456 – login failed for user.”
Pinal: Try sqlcmd -S .\sqlent1 -E
(After a while)
DJ: It works! So you still know SQL Server, what’d you say?
Pinal: I say  you still do not know SQL Server (evil laugh)
DJ: (extends the evil laugh)

Well, it was simple. He was trying to connect to SQL Server which was installed on his local box, where he was the admin. In this case, he can use a simpler script. This is a very specific situation and it was not the production server’s fault. Here is a copy of the scenario on my personal laptop.

Watch SQL in Sixty Seconds video to Resolve Connection Error:

Watch the video on the same subject:

Reference : Pinal Dave (http://blog.SQLAuthority.com)

About these ads

13 thoughts on “SQL SERVER – Connecting to Server Using Windows Authentication by SQLCMD

  1. Thanks for the detailed information.

    Tried before but never worked. After reading your blog, Tried and worked.

    You know that you always rock, right?

    Thanks again.

    Like

  2. I’m facing same problem in SSMS for windows authentication mode connection. My new machine having Windows 7 operating system and SQL 2005.

    Frist time I can able to connect local database server with windows authentication. After that If I connect to test database with use VPN connection, it’s connected with windows authentication. Then again I CANNOT connect the local database with Windows authentication. I’m getting this error:

    Cannot connect to SQL2

    Additional Information:
    Login failed for user ”. The user is not associated with a trusted SQL server connection. (Microsoft SQL Server, Error:18452)

    Like

  3. Pinal,

    I am using Windows Server 2008 R2 and I have SQL Server 2008 installed on the machine. I am logged in as the administrator and it is not allowing me to create an ODBC connection. I receive:

    Connection Failed
    SQLState: ‘28000’
    SQL Server Error: 18456

    I know it has something to do with Windows Server 2008’s security, because installing SQL Server 2008 R2 on a Windows Server 2003 machine does not give me this headache :-)

    Like

  4. You havent explained how it matters. I am facing the same issue, found your solution on it working but not convincing. Why cant I connect using explicit -U and -P parameters to the server?

    Like

  5. I agree Amogh a more detailed explanation is required I am trying to create a format file using BCP, I am able to do it with the sa username and password,
    If I use the -T switch it works fine as well, however when I try to use the username that connects me to SQL Server with Windows Authentication eg -U Servername\User -P XXX I get error
    SQLState = 28000, NativeError = 18456
    Error = [Microsoft][SQL Server Native Client 10.0][SQL Server]Login failed for
    user ‘Server\User’.
    My windows admin username is different from my SQL username however my SQL username uses Windows Authentication to connect to SSMS.

    I have the same problem when I use OPENROWSET, if I use the Trusted_Connection=yes option it works but if I use the Windows Authenticated username and password it fails.

    Like

  6. Pingback: SQL SERVER – Resolving SQL Server Connection Errors – SQL in Sixty Seconds #030 – Video « SQL Server Journey with SQL Authority

  7. In real life, we generally connect database which will hosted on different server. I agree that we can connect local database by forcing SQL Server using “-E” but will this help us? no.. then how we can connect the database which are hosted in different server.

    I spend more than two days on Google and try all possibility but none of them help me to resolve my issue.

    I am able to connect production server database using sql server management studio in my system by inserting servername, username and password but when I try to connect using sqlcmd it always try to select my local database instead of server and throw me error

    Login failed for user ‘username’. The user is not associated with a trusted SQL Server connection.

    Your any small tip or help will be appreciated!!
    I tried may syntex, some of them are.
    Sqlcmd –s servername,portnumber –U username –P Password
    Sqlcmd –s ServerIP –U username –P Password
    Sqlcmd –s serveIP,portnumber –U username –P Password

    Like

  8. Hi Pinal.

    I am facing error with SQLCMD(sql 2005 EE)

    SQLCMD -E -S

    Sqlcmd: Error: Microsoft SQL Native Client : Unable to complete login process du
    e to delay in opening server connection.

    When I tried
    SQLCMD -E -S -l20
    it connected.

    I tried with sql authentication
    SQLCMD -S -U -P

    It worked find
    issue with windows authentication

    Like

  9. Pingback: SQL SERVER – Weekly Series – Memory Lane – #030 | SQL Server Journey with SQL Authority

  10. One SQL Instance with a TEMPDB set larger than the disk size, restarts. When start, is not possible because can not create the tempdb database. After trying to start sqlserver with -M -F I realize that SQLCMD -S -E do not accept my windows system user (to there change the Tempdb size). There is not available a sql sa user. Any ideia to solve?

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s