CODEWITHSUNDEEP
sql-serverazure-active-directoryazure-sql-database
user2756234
user2756234

Reputation: 41

Access to AD group in Azure SQL Database

I have a customer who has created SQL azure database and trying to give access to his on-premise Security group account in database. The challenge that we are facing is that the on-premise group is named as 'group account' i.e. there is a space between the words. This account is synched with Azure AD.

Two questions

1) How do you provide access to a security group on SQL Azure Database? I know that you will say to use syntax like below but that doesn't work in my case i) There is empty space between the words in name ii) this is not a mail enabled group therefore there is no corresponding @domain.com in Azure AD for this group

CREATE USER [[email protected]] FROM EXTERNAL PROVIDER

Upvotes: 0

Views: 2884

Answers (2)

Leon Yue
Leon Yue

Reputation: 16431

Firstly, you should login your Azure database with your AD admin account.

Then run this query(same with juunas provided) to add on-premise Security group to Azure SQL database.

CREATE USER [<Security Group Display Name>] FROM EXTERNAL PROVIDER WITH DEFAULT_SCHEMA = [<schema>];

As you said, there is a space in your 'group account' name, I tried to the same operation and it work ok with no error.

For example, I login my Azure SQL database with my AD admin, ran this query to create a group accout 'test gp'.

CREATE USER [test gp] FROM EXTERNAL PROVIDER WITH DEFAULT_SCHEMA = [dbo];

Running SELECT * FROM SYS.DATABASE_PRINCIPALS we could see that the group has been created in Azure SQL Database

enter image description here

I executed the TSQL: EXEC sp_addrolemember 'db_owner', 'test gp';

It seems everything is ok but when disconnect my SQL database and try to login with group account 'test gp@****.com'

There is an error happened: enter image description here

You can also reference these blogs:

  1. Unable to add Azure AD group with ':' in display name to Azure SQL, am I missing something?
  2. Naming conventions in Active Directory for computers, domains, sites, and OUs

I think Azure SQL database doesn't support using Security group name with special character to login the database, such as white space (blank).

Hope this helps.

Upvotes: 2

juunas
juunas

Reputation: 58863

I've used CREATE USER [Group Name] FROM EXTERNAL PROVIDER before and it has worked.

Upvotes: 1

Related Questions