How To Change User Id And Password For Azure SQL Server Database

Well, let’s discuss here the easiest way to change the user id and password for the Azure SQL server database. But before that, you should have a little bit of an idea of What is Azure SQL Database?.

Azure SQL Database Change User Password

Assuming, you have enough idea on Azure SQL Database by now. Let’s discuss the steps to reset the password for Azure SQL Server Database.

How To Reset Password For Azure SQL Server Database

Log in to the Azure Portal (https://portal.azure.com/).
Once, you have logged in to the Azure Portal, search for SQL Server, and then click on the search result SQL Server.

change user id and password for azure sql server database

3. On the SQL servers page, you can able to see the list of databases that you have created already, Click on the one for which one, you want to change the user id and password.

Steps to change user id and password for azure sql server database

4. On the overview tab of your SQL server database page, click on the Reset password button as highlighted below.

How to reset password for azure sql server database

5. On the Reset admin password window, enter the new password and confirm the password and then click on the Save button to update the password or you can click on the Discard button to discard the changes.

How to reset password for azure sql database

6. Once, you will click on the Save button, it will take few seconds to update the new password and then it will show you the message that “Successfully reset administrator password”.

reset password for azure sql server database

Note: An important point to remember here is, the admin login name can’t be changed once it has been created. You can only able to change the password.

This is How To Change User Id And Password For Azure SQL Server Database.

How To Change User Id And Password For Azure SQL Server Database Using PowerShell

You can also reset the password of your Azure SQL Server Database using PowerShell. Follow the below steps to Change User Id And Password For Azure SQL Server Database Using PowerShell.

Open the PowerShell ISE using Run as Administrator mode.

How To Change User Id And Password For Azure SQL Server Database Using PowerShell

2. Now, run the Below PowerShell Script to reset the Password For your Azure SQL Server Database.

$pwd = ConvertTo-SecureString -AsPlainText -Force 'Raj@######'
Set-AzureRmSqlServer -ResourceGroupName Demo123 -ServerName test45 -SqlAdministratorPassword $pwd

Where, Demo123 is the Resource Group name, test45 is the Azure SQL Server Database name in my case. Make sure to change the resource group and database server name based on yours.

Once you will run the above script, It will reset the password for your Azure SQL Server Database successfully and you will get the below Output.

ResourceGroupName        : Demo123
ServerName               : test45
Location                 : eastus
SqlAdministratorLogin    : rajkishore
SqlAdministratorPassword : 
ServerVersion            : 12.0
Tags                     : {}
Identity                 : 
FullyQualifiedDomainName : test45.database.windows.net
ResourceId               : /subscriptions/1cdf4300-dee5-4518-9c9c-feaa72a5cbd1/resourc
                           eGroups/Demo123/providers/Microsoft.Sql/servers/test45
MinimalTlsVersion        : 
PublicNetworkAccess      : Enabled

Check out this as below

How To Change Password For Azure SQL Server Database Using PowerShell

This is How To Change User Id And Password For Azure SQL Server Database Using PowerShell.

How To Change User Id And Password For Azure SQL Server Database Using Azure CLI

Well, here we will discuss How To Reset Password For Azure SQL Server Database Using Azure CLI. Follow the below steps to change the password for Azure SQL Server Database using Azure CLI.

Log in to the Azure Portal (https://portal.azure.com/).
Click on the Cloud Shell button as highlighted below.

Change User Id And Password For Azure SQL Server Database Using Azure CLI

3. Paste the below script on the Cloud Shell window to change the password of the Azure SQL Server Database.

az sql server update --resource-group Demo123 --name test45 --admin-password Raj@@@@@@

Where Demo123 is the name of the resource group and test45 is the Azure SQL Server Database name. Make sure to change the resource group and Azure SQL Database server name based on yours.

Once you will run the above script, it will immediately change the Pass Word of your Azure SQL Server Database, you will get the below output

PS /home/bijay> az sql server update --resource-group Demo123 --name test45 --admin-password Raj@@@@@@@
{
  "administratorLogin": "rajkishore",
  "administratorLoginPassword": null,
  "fullyQualifiedDomainName": "test45.database.windows.net",
  "id": "/subscriptions/1cdf4300-dee5-4518-9c9c-feaa72a5cbd1/resourceGroups/Demo123/providers/Microsoft.Sql/servers/test45",
  "identity": null,
  "kind": "v12.0",
  "location": "eastus",
  "minimalTlsVersion": null,
  "name": "test45",
  "privateEndpointConnections": [],
  "publicNetworkAccess": "Enabled",
  "resourceGroup": "Demo123",
  "state": "Ready",
  "tags": {},
  "type": "Microsoft.Sql/servers",
  "version": "12.0"
}

You can see the output as below

This is How To Change User Id And Password For Azure SQL Server Database Using Azure CLI.

How To Reset Password For Azure SQL Managed Instance

Same way, it’s quite easy to reset password for Azure SQL Managed Instance using the below steps.

Log in to the Azure Portal (https://portal.azure.com/).
Once, you have logged in to the Azure Portal, search for SQL Managed Instance, and then click on the search result SQL Managed Instance.

How To Reset Password For Azure SQL Managed Instance

3. On the SQL Managed Instance page, you can able to see the list of SQL Managed Instances that you have created already, Click on the one for which one, you want to reset the password.

4. On the overview tab of your SQL Managed Instance database page, click on the Reset password button as highlighted below.

5. On the next window, enter the new password and confirm the password and then click on the Save button to update the password.

This is How To Reset Password For Azure SQL Managed Instance.

How To Create A User For Azure SQL Database

If you want to create a user in the Azure SQL database, it’s quite easy. You can follow the below steps to create a user in the Azure SQL database.

Creating a user in the Azure SQL Database involves the below key steps

The first thing is you need to connect to the Azure SQL Database server with the SQL Server Management Studio as an admin in Master.
Now you need to create a login (SQL Authentication) called “xyz” with a Password “Abc@1234”.
The next step is to create a user and then you need to map the user to the login XYZ that you have created before.
Finally, You need to add it to the db_datawriter and db_datareader roles.

Make sure to run the below query on the Master

CREATE LOGIN xyz 
WITH PASSWORD = 'Abc@1234'

Now choose the DB as User and run the below query

CREATE USER [xyz] 
FOR LOGIN [xyz] 
WITH DEFAULT_SCHEMA = dbo; 
  
-- adding user to roles
ALTER ROLE db_datareader ADD MEMBER [xyz]; 
ALTER ROLE db_datawriter ADD MEMBER [xyz];

This is How To Create A User For Azure SQL Database.

SQL Azure Check User Permissions

Follow the below steps for checking the permissions for Azure SQL users.

Open the SQL Server Management Studio (SSMS).
On the Connect to Server window, provide the below details

Server type: Select the Server type as Database Engine
Server name: Provide your Azure SQL database server name.
Authentication: You can choose the Authentication option as SQL Server Authentication
Login: Provide the login name.
Password: Provide the password.

Finally, click on the Connect button.

3. Select your Database and click on the New Query button.

4. Paste the below SQL script and click on the Execute button.

SELECT DISTINCT pr.principal_id, pr.name AS [UserName], pr.type_desc AS [User_or_Role], pr.authentication_type_desc AS [Auth_Type], pe.state_desc,
    pe.permission_name, pe.class_desc, o.[name] AS 'Object' 
    FROM sys.database_principals AS pr 
    JOIN sys.database_permissions AS pe ON pe.grantee_principal_id = pr.principal_id
    LEFT JOIN sys.objects AS o on (o.object_id = pe.major_id)

5. Once you will execute the above script, you can able to see the output as below.

This is all about SQL Azure Check User Permissions.

Azure SQL Authentication

Let’s discuss an overview of Azure SQL Authentication. Before that we should know what exactly authentication is?.

Authentication is the process to recognize the identity of the user. Coming back to the Azure SQL Authentication, Azure SQL Authentication basically supports two types of authentication. Those are as below

1. SQL Authentication

The simple and basic authentication that the Azure SQL Database support is SQL Authentication. SQL authentication is an authentication mechanism where the user has to provide the username and password.

While creating the Azure SQL Database server, you need to provide the username and password. That username and password you have to use now.

2. Azure Active Directory authentication

This is another excellent authentication mechanism that helps you to connect to the Azure SQL Database by using the Azure Active Directory identity or Azure AD identity.

It provides you high security compare to the SQL Authentication mechanism. As we know that using password always is not a good idea to secure your data. Azure Active Directory authentication minimizes the practice of using passwords.

The parameter password cannot be provided for users that cannot authenticate in a database.

There might be a change while running the script to manage users, you will face an error like the parameter password cannot be provided for users that cannot authenticate in a database.

In order to fix this error, you must make sure that while creating an user in Azure SQL database server, you have done in a proper way.

You must use the script like below

CREATE LOGIN xyz 
WITH PASSWORD = 'Abc@1234'

If you won’t create the user in a proper manner then there is a chance that you will get this error. You can check out the above section for more details on How to create the user in Azure SQL Database.

SQL Azure Create Login Default Database

When you are trying to connect to the Azure SQL Database from the SQL Server Management Studio, If you want to connect to a new database and not the default one then follow the below steps.

Click on the Options >> button on the Connect to Server window.

2. On the Connection Properties tab, write the new database name instead of <default> on the Connect to database option. Finally, click on the Connect button to connect to the new database.

Create SQL Login and SQL User on your Azure SQL DB

You may also like following the below Articles

Wrapping Up

In this article, we have discussed How To Change User Id And Password For Azure SQL Server Database, How To Change User Id And Password For Azure SQL Server Database Using PowerShell, How To Change User Id And Password For Azure SQL Server Database Using Azure CLI, How To Reset Password For Azure SQL Managed Instance. Along with this, we have also discussed How To Create A User For Azure SQL Database, SQL Azure Check User Permissions, Azure SQL Authentication, The parameter password cannot be provided for users that cannot authenticate in a database, SQL Azure Create Login Default Database. Hope you have enjoyed this article !!!