Wednesday 11 November 2020

How To create SSIS user in Azure SQL server

I've assumed you have already configured domain in azure and have created a domain user.

Create Application in app registration  

First of all Create an app registration [myssissqlserver01]


Set up Active directory Admin in sql server

Now Go to the sql server (not the database) and on settings menu, click Active directory Admin



Click Set Admin and add your domain account. (in case you don't have domain configured , Configure a new domain and create a domain user first)

Connect to the database using MS Sql server management studio

Once admin is set , Open MS Sql server management studio and connect to the database.



Since we have added user to active directory admin, the user will have access to master database. So we can choose default database to connect, 


Create a user in ssis and add to a suitable role

Create a new query window selecting SSISDB database

execute the below queries. Note that the user name should be identical to the Application name created in App registration section . [ myssissqlserver01]

create user [myssissqlserver01] from external provider;

exec sp_addrolemember [db_owner],[myssissqlserver01];


Generate SQL script from entity framework using powershell/visual studio

to run the below command , start PowerShell in visual studio. Select the database project. In PowerShell:   Script-Migration this command wi...