I just finished installing SQL Server 2016 (The full thing — not just Express). I specified Windows authentication. During the install in the install wizard, in the Database Engine Configuration node and on the Server Configuration tab, I simply clicked the Add Current User button. I didn't add a specific user for administration at install time.
Before posting, I also took a look at this: https://msdn.microsoft.com/en-us/library/ms180944.aspx
Anyway, when I run this from a command prompt:
C:\Users\dogzilla>sqlcmd -S dogzilla -U dogzilla
I get this error:
Password: Sqlcmd: Error: Microsoft ODBC Driver 13 for SQL Server : Login failed for user 'dogzilla'..
dogzilla is the account I use to log into Windows with. I know the password since I'm able to write this to you.
What's going on with the authentication? Does the dogzilla password need to be set in SQL Server? How can I change it there?
Thanks!
Best Answer
If you're connecting to a local instance with Windows authentication you specify -E (or nothing, it's the default). Specifying -U will attempt to login with a SQL login.
Here's an example where I connect to a local named instance using Windows auth and run a simple command.