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 about SQLCMD.

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.

SQL SERVER - Connecting to Server Using Windows Authentication by SQLCMD sqlcmderror

Watch SQL in Sixty Seconds video to Resolve Connection Error:

Watch the video on the same subject:

Reference : Pinal Dave (https://blog.sqlauthority.com)

SQL Scripts, SQL Server, SQL Utility, sqlcmd
Previous Post
SQL SERVER – Delete Duplicate Rows
Next Post
SQL SERVER – Denali CTP3 – Step by Step Installation Video – 200 Seconds

Related Posts

12 Comments. Leave new

  • 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.

  • 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)

  • 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 :-)

  • Amogh Sarpotdar
    July 22, 2012 9:23 pm

    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?

  • 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.

  • Reply
  • Dharmesh Barochia
    January 11, 2013 11:59 pm

    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

  • Guys, MS says -U and -P switches are for SQL authentication; therefore, you can’t use them with domain logins:

  • 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

  • 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?

  • My local windows user is a member of Administrator group, tried to sqlcmd with -E and it is connected.
    I tried to run a sql script and got the error in logs. Here is the command I used :

    >sqlcmd -S Server -E -i script.sql -o output.txt

    Here is the error in log :

    Msg 916, Level 14, State 1, Server Server\SQLEXPRESS, Line 1
    The server principal “Server\User” is not able to access the database “database” under the current security context.

    P.S. If I run the same script with “Administrator” user, it worked.

    Please help how I can allow my user to run queries and scripts under sqlcmd, as I dont want to use username and password to connect sqlcmd and run scripts.


Leave a Reply