I Have A SQL Login - Why Can't I Connect?

Published Tue 09 January 2018 in SQL > TSqlTuesday

tsqltuesdaylogo

This post is a response to this month's T-SQL Tuesday prompt created by Arun Sirpal . Adam Machanic created T-SQL Tuesday as a way for SQL users to share ideas about interesting topics. This month's topic is "Your Technical Challenges Conquered" .

DBA Skills 101: SQL Logins

While writing last week's post about efficiently scripting database objects , I decided to make progress towards my 2018 learning goals by figuring out what database permissions were needed for running SQL Server Management Studio's "Generate Scripts" tool.

I thought it would be best to start with a clean slate so I created a new SQL login and database user so that I could definitively figure out which permissions are needed.

Normally I use Windows Authentication for my logins, but this time I thought "since I'm getting crazy learning new things, let me try creating a SQL Login instead."

After I created my login, I decided to test connecting to my server before digging into the permissions.  Result?

Watch this week's video on YouTube

I can't connect!

That's right, I tried to connect and I got this very detailed error message

:

Login failed for user Microsoft SQL Server Error: 18456

"Great," I thought.  "I should just switch to a Windows Authentication login, those always work for me."

"BUT NO, THEN I WON'T LEARN ANYTHING!"

On to troubleshooting

First things first, I tried retyping my login and password (I know typing in the password of "password" is really tricky but I've made mistakes doing much simpler things).

No luck.  Maybe when I created the login I fat-fingered the password?

I recreated the login, making sure I precisely typed the password.  Try to connect again...nope.

Ok, ok.  I'm missing something obvious.  I have this error message though - maybe the internet will know!

I find the exact error message in a blog post by Aaron Bertrand - he's a credible guy, I bet I'll find the solution there!

Nope.

(Side note: the answer is there, just buried in the comments.  In my eager "this will be an easy solution" I didn't bother scrolling down that far).

Ok... how about books online?  Even though I created the login through the SSMS GUI, I know the T-SQL command to do the same is CREATE LOGIN .  Maybe I'll find the solution in the documentation?

No luck.

(Side note again: in hindsight you can get to the solution from the above link, but it's buried two further links deep.  While troubleshooting I was in the mindset of "ain't nobody got time for that" - I wanted a solution given to me immediately without having to do any further research!)

I kept searching online, reading through Stack Overflow answers, not finding what I needed.

(Side note (last one, I promise): anyone else having a harder time searching for relevant Stack Overflow answers?  It feels like more and more I find questions/answers that are for older versions and no longer relevant)

At this point I was really frustrated. "CREATING A LOGIN SHOULD BE LIKE THE FIRST THING A DBA LEARNS!! WHY IS THIS SO HARD?!?!?!?!?!?!"

At that point I was tired and disappointed that I had spent more time trying to solve this login problem rather than actually figuring out the permissions that I wanted to include in my blog post.

Sleep

I decided to take a break for the night and revisit the problem the next morning.

As expected, I searched the internet for the answer again and somehow my keyword selection hit the jackpot - I found the Stack Overflow answer telling me I needed to set the server to mixed authentication mode :

mixed-authentication-mode

Wow, that was easy.

Takeaway

This wasn't a complex problem.  At least, it shouldn't have been a complex problem.

All in all I spent probably 30 minutes trying to figure it out - not the longest amount of time I've sunk into a problem that ended up having a really simple solution.

However, this stuff happens.  It's amazing what a fresh (rested) set of eyes can do for solving a problem.

Lesson learned: next time I'm getting frustrated by a problem that I think should be easy to solve, I need to step away from the computer and come back once I have a clearer mindset :).

One Last Technical Challenge (BONUS)

I figured I'd add one more technical challenge to this post: submit a pull request to the sql-docs GitHub .

My rationale was that I couldn't be the only person to have ever been stumped by authentication modes.  Maybe I could be helpful to the next person who visits the CREATE LOGIN books online page and give them a hint as to why they can't connect.

Contributing to open source isn't something I've done through Github before, but luckily I had Steve Jones's excellent write up to guide me.

There were no real challenges here since I was just making a simple edit, and low and behold a few days later my PR got merged and is now live in BOL - cool!