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.
Watch SQL in Sixty Seconds video to Resolve Connection Error:
Watch the video on the same subject:
Reference : Pinal Dave (https://blog.sqlauthority.com)
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 :-)
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.
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
try using -S instead of -s
Guys, MS says -U and -P switches are for SQL authentication; therefore, you can’t use them with domain logins:
https://docs.microsoft.com/en-us/powershell/module/sqlserver/invoke-sqlcmd?view=sqlserver-ps&viewFallbackFrom=sql-server-2017
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.