SQL Login Error after Migration to Azure SQL - Error 18456

Question

Fellow SQLers,

I have migrated a SQL 2016 db to Azure SQL. Went great.
Before I migrated, I had a SQL Login for the db - worked great. It was given dbo to the db.

After the migration, when logged into the Azure DB with my personal Azure login/password with ssms it works fine.

But, I am not able to connect to the Azure db with SSMS on my local laptop using that SQL Login
I get the 18456 error.

Does the SQL Login have to be added to some collection in Azure to connect with SSMS?

Thanks
MG

Accepted Answer

Team,

I found the answer. What I was missing was found in a youtube. The issue was I had not loaded the login in all of the needed places and the db role had to be set as well.
The youtube is https://www.youtube.com/watch?v=SDBy9JjsZBw
After the login was created in Master. I needed to create a User for it in Master.
Then user needed to be created in the actual db. Then I gave it the db role I needed in the actual db.
That was it. I thought that a lot of that had been carried thru in the migration - nope.

from my testing, if you do not want to give the login a user to master, then you would have to specify the DB name in the connection string using SSMS.

Mark

Answer

Hi @Mark Gordon , What we are dealing here is orphaned users. These are the users that get created when we migrate database from one server to another. You can refer to article for more details. Below is the excerpts from the article:
"The login object has a security identifier called SID, which uniquely identifies it on the specific SQL server instance. This SID column is placed, as a foreign key value, at the login’s associated database user, in each database that the login has access and is a member of.
Creating another SQL login that resides on a different SQL server instance and has the same name, will not, as one might expect, recreate the same SID."

There are ways to resolve it as mentioned in the below article. However, if we are dealing with very few users and login, I would recommend you to recreate them and try accessing the database then.
https://github.com/uglide/azure-content/blob/master/articles/sql-database/sql-database-copy-transact-sql.md#:~:text=After%20the%20new%20database%20is,database%20security%20after%20disaster%20recovery.

Please let me know if this helps you or else we can discuss more on the same.

----------

If an answer is helpful, please "Accept answer" or "Up-Vote" for the same which might be beneficial to other community members reading this thread.

Answer

Goo day,

Fellow SQLers,

So... I am an SQLer

I love it... I need a T-Shirt saying " I am an SQLer!" ... But I am also Mongodber, and CosmosDber, and other data platformer... well I need to think about wht best fit for the T-shirt. In the meantime lets go back to the question

Before I migrated, I had a SQL Login for the db

No, you did not. A LOGIN is an instance level entity and not database level entity. You use LOGIN in the intance and USER in the database.

It was given dbo to the db.

I have no idea what you mean here since dbo is a name of built-in schema and also a name of a built-in USER.

I get feeling like you have a bit of confusion regarding SQL Server terms.

What is "dbo" ?

SQL Server Schema is a logical container (collection) of database objects.

Using schema is a useful security mechanism to segregate database objects for better management. Schemas can be owned by any database principal, and each USER in the database has a setting for a default Schema.

The schema is part of the hierarchy Object model, starting with the Server Instance name -> Database name -> Schema name -> and the object name.

Each object can be referred using "four-part naming" based on the hierarchy Object model using the format .... For example, we can refer to a table named "Tbl" which is in schema "Scm" which is in Database "DB" in instance "sql2019" like: sql2019.DB.Scm.Tbl

SELECT * FROM sql2019.DB.Scm.Tbl

SQL Server model database (which is used as the template for all databases) ships with several pre-defined schemas that have the same names as pre-defined database users or pre-defined database roles.

Note! Using the same name for different entities is VERY confusing and probably a very bad approach in my opinion.

The name "dbo" is one of these mentioned names. We have a USER named "dbo" and we have SCHEMA named "dbo".

Nothing from the above information is directly related to your issue.

After the migration, when logged into the Azure DB with my personal Azure login/password with ssms it works fine.

Each Azure SQL logical server starts with a single server administrator account that is the administrator of the entire server. This account is not related to the Azure login/password.

The Azure login/password are entities in the Azure Active Directory (AAD). If your description is accurate then it seems like your AAD was configured as second administrator account, which is not the default.

What next?!?

But, I am not able to connect to the Azure db with SSMS on my local laptop using that SQL Login

I get the 18456 error.

(1) Please provide the full error message since we cannot read minds and most of us do not remember all the numbers of all the errors. The error 18456 is a generic Login Failed error message.