Azure-docs: SQL Authentication is the only option from SSIS Azure and SSAS Azure

[ElectricLlama]

For PaaS deployments of SSIS and SSAS (That is within _Azure Data Factory SSIS runtime_, and _Azure Analysis Services_) that read or write data to SQL Azure, the _only_ authentication option is SQL Authentication.

• We can configure various methods of AD authentication within SQL Azure

• We can also configure MSI within Azure Data Factory and Azure Analysis Services to emulate the 'service account' concept.

• We can grant that MSI access to a SQL Azure database

• We can also use set_execution_credential to define an identity to access on-premises resources

But what we _can't_ do is

• Create a connection in an SSIS package that can connect using the MSI
• Create a data source in Azure Analysis Services that can utilise the MSI

The result is that we need to go back to the bad old days of using SQL Authentication when migrating from on-premises to cloud.

---

Document Details

⚠ Do not edit this section. It is required for docs.microsoft.com ➟ GitHub issue linking.

• ID: 55747c48-5caa-3234-f978-a667aed13352
• Version Independent ID: c18a4db6-71e3-a539-1e7f-2f49662f4bbc
• Content: Azure Active Directory auth - Azure SQL (Overview)
• Content Source: articles/sql-database/sql-database-aad-authentication.md
• Service: sql-database
• GitHub Login: @GithubMirek
• Microsoft Alias: mireks

[Mike-Ubezzi-MSFT]

@ElectricLlama Thanks for the feedback. We are actively investigating and will get back to you but, I did want to mention that MSI is not supported by Azure SQL Database. MSI is supported when Azure SQL is deployed within an Azure VM (IaaS). Let me double-check a few things and I will get back with you. Regards, Mike

[Mike-Ubezzi-MSFT]

@ElectricLlama It appears that AAD authentication is available. Please review these Prerequisites:

You can connect to the database server using SQL authentication with your server admin credentials or Azure Active Directory (AAD) authentication with your Azure Data Factory (ADF) Managed Service Identity (MSI). For the latter, you need to add your ADF MSI into an AAD group with access permissions to the database server, see Create Azure-SSIS IR with AAD authentication.

Please review review the documentation: Create Azure-SSIS IR with AAD authentication

Regards,
Mike

[Mike-Ubezzi-MSFT]

@ElectricLlama We will now proceed to close this thread. If there are further questions regarding this matter, please reopen it and we will gladly continue the discussion.

[ElectricLlama]

Not sure how I reopen this, but I'm not asking how to provision a SSIS runtime under the MSI. I've done that already. The problem is that you cannot build and publish a package to the SSIS runtime that uses the MSI to connect to SQL Azure. To put it another way, if I build an SSIS package in SQL Server Data Tools, there is no authorisation method (for example in a ADO.Net connection manager) that will let you utilise the MSI to connect to SQL Azure.

The result is that any on-premises SSIS packages that are currently neatly bound up using windows service account credentials now have to be converted to use SQL credentials if you want to run them in Azure.

[Mike-Ubezzi-MSFT]

@ElectricLlama Thanks for the feedback! I have assigned the issue to the content author to evaluate and update as appropriate.

[Mike-Ubezzi-MSFT]

@GithubMirek Is there an opportunity here to provide supported/unsupported scenarios in the documentation?

[ElectricLlama]

It's been a while but: I'm now successful using Authentication=Active Directory Password instead of SQL authentication in my SSIS packages. This is not supported in OLEDB, only ADO. This still requires an actual password in the connection string but at least it does not require a password to be defined on the database side.

[swinarko]

@ElectricLlama thanks for the feedback, we’re actually already planning to add AAD Auth w/ the managed identity for ADF in SSIS package executions, but no concrete ETA yet.

[CarlRabeler]

@ElectricLlama If you would like to file a product feature request and track it / get your peers to vote on it, go to https://feedback.azure.com/forums/908035-sql-server
Thanks,
carl

[CarlRabeler]

please-close

[Mike-Ubezzi-MSFT]

We will now proceed to close this thread. If there are further questions regarding this matter, please comment and we will gladly continue the discussion.

[elangokp]

Team, do we have a solution for using active directory integrated mode authentication from packages running inside Azure SSIS IR?

[swinarko]

We won't support AD Integrated Auth, but we'll support AAD Auth w/ ADF managed identity for packages running on SSIS IR (ETA this month).

[elangokp]

@swinarko Thanks for the quick response.

[richard-nixon1]

@swinarko what is the latest on this please?

[swinarko]

OLEDB/ADO.NET Connection Managers that can use AAD Auth w/ ADF managed identity to connect to Azure SQL DB/Managed Instance went live last week, a blog will be published soon, here are our docs:

https://docs.microsoft.com/sql/integration-services/connection-manager/ole-db-connection-manager?view=sql-server-2017#managed-identities-for-azure-resources-authentication

https://docs.microsoft.com/sql/integration-services/connection-manager/ado-net-connection-manager?view=sql-server-2017#managed-identities-for-azure-resources-authentication

Please have a try and let us know if you have any feedbacks/questions.

[richard-nixon1]

@swinarko thanks for your swift response! I'll give it a try now.

[Mike-Ubezzi-MSFT]

@swinarko Can you please doublecheck your (duplicate) links and provide the link for the blog post, too! Thanks!

[swinarko]

Those are different links for OLEDB/ADO.NET and our blog combining this feature with other migration unblocking features will be published soon.

[richard-nixon1]

@swinarko has this fix been applied to Azure Analysis Services also? If so could you provide more detail on the AS side please.

I am still receiving an error as per this thread.

[swinarko]

Unfortunately, I only cover SSIS/ADF, so please raise your questions on AAS forum/doc pages.

[Mike-Ubezzi-MSFT]

Please make sure the following requirements are met on the host the connection is being attempted from.

[richard-nixon1]

@swinarko I've managed to setup an SSIS IR using AAD Auth w/ ADF managed identity however when I trigger a package run from ADF I'm receiving an error when the package tries to connect to a Azure SQL database source using OLE DB connection:

Error: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred.
Error code: 0x80040E4D. An OLE DB record is available.
Source: "Microsoft OLE DB Driver for SQL Server"
Hresult: 0x80040E4D
Description: "Cannot use Access Token with any of the following options: Authentication, Integrated Security, User, Password."

My connection string looks like this (I have set the property ConnectUsingManagedIdentity = True):

Data Source=xxxxxxxxxxx.database.windows.net;Initial Catalog=xxxxxxxxxxx;Provider=MSOLEDBSQL;Authentication=ActiveDirectoryIntegrated;

Could you provide an example of a OLE DB connection string that you have got working during your testing please?

Also are there any other settings required to get AAD Auth w/ ADF managed identity connecting to an Azure SQL database source/destination within the package?

[swinarko]

Did you set the "ConnectUsingManagedIdentity" property of your OLEDB CM = True and assign the AAD group containing your ADF managed identity the correct role to access your SQL DB? Please follow the instructions on https://docs.microsoft.com/sql/integration-services/connection-manager/ole-db-connection-manager?view=sql-server-2017#managed-identities-for-azure-resources-authentication. If it still doesn't work, email me ([email protected]).

[pavankumarda2136]

Hello Team, is there any way to change authentication method to connect sql db from username to service principal in sql server database ?