Assembly creation in SQL Server for azure managed instance with MFA authentication

Question

Assembly creation in SQL Server for azure managed instance with MFA authentication

Abhishek Modi (amodi) 20

Hi Team,

During the creation of an assembly, we require some field information from sys.[sql_logins] view. These fields are such as SID, name, principal_id, type, type_desc, create_date, modify_date, default_database_name, and default_language_name, etc. This information is not present in this view in the case of logged-in users of Azure Managed Instance.

Can you please let us know from where we should fetch this information or any other alternative where we could get those details in case of Azure Managed Instance?

Thanks,

Abhishek Modi

GeethaThatipatri-MSFT 29,597 Reputation points Microsoft Employee Moderator

2024-03-15T14:27:10.0066667+00:00

Hi, @Abhishek Modi (amodi) Welcome to Microsoft Q&A thanks for posting your question.

In Azure SQL Managed Instance, you can use the sys.server_principals and sys.database_principals views to fetch information about server-level and database-level principals

Regards

Geetha
GeethaThatipatri-MSFT 29,597 Reputation points Microsoft Employee Moderator

2024-03-18T15:11:44.38+00:00

@Abhishek Modi (amodi) I am checking to see if you got a chance to look into my above response. Please let me know if you have any further questions.

Regards

Geetha
Abhishek Modi (amodi) 20 Reputation points

2024-03-20T05:55:33.6366667+00:00

Thanks, @GeethaThatipatri-MSFT for the response.

I went through sys.server_principals and sys.database_principals views I found relatively information or data regarding the concern I posted but there are few other fields/column details required, these I won't found in these mentioned views such as - password_hash, is_policy_checked and is_expiration_checked.

Can you please update on above mentioned three fields from where we should fetch this information or any other alternative where we could get those details in case of Azure Managed Instance?

Sincerely Thanks,

Abhishek Modi
Olaf Helper 47,621 Reputation points

2024-03-20T06:32:39.14+00:00

You can get the other informations with the system function LOGINPROPERTY, see https://learn.microsoft.com/en-us/sql/t-sql/functions/loginproperty-transact-sql?view=sql-server-ver16
Abhishek Modi (amodi) 20 Reputation points

2024-03-21T14:33:59.53+00:00

Hi @Olaf Helper / @GeethaThatipatri-MSFT , Thanks for the sharing information and reference link.

According to the shared learning article example, I tried to fetch Password_Hash but it is returning System.Byte[] or NULL for every case.

For example:

SELECT LOGINPROPERTY('John3', 'Password_Hash');

GO

Can you please update on how or from where to fetch Password_Hash value or any other alternative where we could get these details in case of Azure Managed Instance?

Sincerely Thanks,

Abhishek Modi
GeethaThatipatri-MSFT 29,597 Reputation points Microsoft Employee Moderator

2024-03-22T14:39:42.01+00:00

@Abhishek Modi (amodi) sys.[sql_logins] should contain information related to sql logins only and it will not have information for AAD logins. You don’t see the details for sql logins too in MI?I wonder if you are using Microsoft Entra only for authentication.

Regards

Geetha
GeethaThatipatri-MSFT 29,597 Reputation points Microsoft Employee Moderator

2024-03-25T14:32:36.5266667+00:00

@Abhishek Modi (amodi) I am checking to see if you got a chance to look into my above response.

Regards

Geetha
Abhishek Modi (amodi) 20 Reputation points

2024-03-26T05:17:25.3366667+00:00

Hi @GeethaThatipatri-MSFT , Posted query is specifically for Azure Managed Instance. View - sys.[sql_logins] does not have fields/information of Microsoft Entra only for authentication users, It has information related to sql logins only.

Sincerely Thanks,

Abhishek Modi