lets start with the basic steps to assign permissions for Azure Data Factory to have access to Azure SQL.
once you have the azure SQL server/ Database created you need to
- Create a AD user that has been given admin permission to the Azure SQL server
2. the next step is to make this user as the admin on the SQL server – use the set admin
3. log into SQL Server management studio using the AD user credentials . The reason you need to do step 1 and 2 , is that you cannot grant access to a Azure AD account from a SQL log in . you will get this error below
Only connections established with Active Directory accounts can create other Active Directory users.
so once you log into SQL management studio with the AD account , you can create a user account for the data factory and assign a role
CREATE USER testdfsjvz FROM EXTERNAL PROVIDER;
alter role db_writer ADD MEMBER [testdfsjvz]
4. Create a linked service in Azure Data Factory and test connection
and there you have it , you just created an linked service to Azure SQL