CODEWITHSUNDEEP
azure-sql-databaseazure-rm-template
sbolla
sbolla

Reputation: 711

Unable to connect the sql database using azure

I have a SQL Server in Azure which is created using a simple ARM template. When I connect to the SQL Server using my sqladmin credentials I am able to connect to it fine. I am using SQL Server Management Studio for this.

enter image description here

However, When I use any of the following highlighted options, I am seeing an error message. enter image description here

The Error I am seeing is as shown below, its a pop-up window but I am pasting the text in it for easy reference.

TITLE: Connect to Server
------------------------------
Cannot connect to abcd-core-sql.database.windows.net.
------------------------------
ADDITIONAL INFORMATION:

**Login failed for user '<token-identified principal>'. (Microsoft SQL Server, Error: 18456)**

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&EvtSrc=MSSQLServer&EvtID=18456&LinkId=20476

I would appreciate if anyone can comment on if I am missing anything in creating a SQL Server using the ARM template.In other words is there anything else that needs to be included in the config to get the azure ad authentication working for a sql db in azure.

    {
        "type": "Microsoft.Sql/servers",
        "name": "[variables('sql_name')]",
        "apiVersion": "2015-05-01-preview",
        "location": "eastus2",
        "properties": {
            "administratorLogin": "[parameters('admin')]",
            "administratorLoginPassword": "[parameters('passwd')]",
            "version": "[variables('sqlversion')]"
        }
    },

Upvotes: 1

Views: 3183

Answers (3)

rod_burim
rod_burim

Reputation: 31

I had the same problem, to fix, you need to click on the button then on the tab and on the field you have to replace with the database name you want/have access.

The issue happens because the default database for the user is the master database but the user doesn't have access to the master database.

Another option is to give the user access to the master database, but usually you don't want this.

Upvotes: 1

DreadedFrost
DreadedFrost

Reputation: 2968

For the ARM template here is what can be added inside the sql Server Resource:

  "resources": [
    {
      "name": "ActiveDirectory",
      "type": "administrators",
      "location": "[parameters('location')]",
      "apiVersion": "2019-06-01-preview",
      "properties": {
        "administratorType": "ActiveDirectory",
        "login": "[parameters('sqlADadmin')]",
        "sid": "[parameters('sqlADadminObjectID')]",
        "tenantId": "[subscription().tenantId]"
      },
      "dependsOn": [
        "[resourceId('Microsoft.Sql/servers', variables('sqlServerName'))]"
      ]
    }

Here is the API documentation

Not you need both the Azure AD objectId and the login name of the account/group you are making sql admins.

This can be obtain via Get-AzureADUser in Powershell or in the Portal

Upvotes: 0

David Browne - Microsoft
David Browne - Microsoft

Reputation: 89091

You must configure an Azure Active Directory Admin for the instance before you can connect with AAD credentials or create any AAD Database Users. See Configure and manage Azure AD authentication with Azure SQL

Once you've set the AAD Administrator, connect as the AAD Admin or a member of the AAD Security Group set as the AAD Admin and Create contained users mapped to Azure AD identities, eg

CREATE USER [[email protected]] FROM EXTERNAL PROVIDER;

or

CREATE USER [ICU Nurses] FROM EXTERNAL PROVIDER;

Upvotes: 2

Related Questions