I’ve got a brand new SQL database inside my new Azure server and I want to allow access to some users that I have designated with the ‘Contributor’ role, so they can write some SQL code.
However, they cannot authenticate: the connection strings say {your_password_here}, but I can’t reset the password!
here: the greyed out ‘reset password’
As you can probably tell I don’t understand how databases work. I want these people to sign in with their account into the database and edit at the level of permissions assigned.
2
Answers
In simple terms, the contributor role will not allow them to gain access to the database, there are two levels of permissions:
When you created your SQL Server, you had to type in an administrator account, you can either use that account to create a SQL Login for your users, or you can integrate your server with Azure AD. With Azure AD users can log in using their Single Sign-On authentication.
To integrate SQL Server with Azure AD:
https://learn.microsoft.com/en-us/azure/azure-sql/database/authentication-aad-configure?view=azuresql&tabs=azure-powershell#provision-azure-ad-admin-sql-managed-instance
To create a database user based on the users’ SSO use the following syntax:
https://learn.microsoft.com/en-us/sql/t-sql/statements/create-user-transact-sql?view=sql-server-ver16
Within your SQL Server, go to Azure Active Directory and uncheck "Support only Azure Active Directory authentication for this server". The "Reset Password" will then be enabled.