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];

TFS -GIT CertGetCertificateChain trust error CERT_TRUST_IS_UNTRUSTED_ROOT

Problem 1)

2019-07-02T14:19:12.5157398Z fatal: unable to access 'https://tfssite.company.com/tfs/UK_ProjectCollection/SalesPortal/_git/SalesPortalWeb/': SSL certificate problem: unable to get local issuer certificate

2019-07-02T14:19:12.9764145Z ##[error]Git fetch failed with exit code: 128

2019-07-02T14:19:12.9861630Z ##[section]Finishing: Get Sources

In the TFS_Build Server or App server(if build and app server is in the same machine) >

run the script from this url: https://blog.sanjeebojha.com.np/2019/06/git-ssl-certificate-problem-unable-to.html

Once this is done, you should get another problem:

Problem 2)  CertGetCertificateChain trust error CERT_TRUST_IS_UNTRUSTED_ROOT

2019-07-02T14:24:57.1800199Z ##[command]git -c http.extraheader="AUTHORIZATION: bearer ***" fetch --tags --prune --progress --no-recurse-submodules origin

2019-07-02T14:24:57.4540146Z fatal: unable to access 'https://tfssite.company.com/tfs/UK_ProjectCollection/SalesPortal/_git/SalesPortalWeb/': schannel: CertGetCertificateChain trust error CERT_TRUST_IS_UNTRUSTED_ROOT

2019-07-02T14:24:57.5198064Z ##[error]Git fetch failed with exit code: 128

2019-07-02T14:24:57.5288511Z ##[section]Finishing: Get Sources

  

1. Export the certificate public key to a file. The file is later required.
2. Open the url "https://tfssite.company.com/tfs/projectcollection" in IE
3. Click on the Lock icon in the browser address bar
4. 
5. 
6. 
7. Select The Root level Certificate and Click View Certificate
8. 
9. 
10. 
11. 
12. 
13. 
14. 
15. 

Now Find the certificate file from GIT folder. 

normally it's inside GIT\usr\ssl\certs folder

1. Copy the file to User folder

copy ca-bundle.trust.crt c:\Users\svc_tfs17_app

2. Config Git to use trusted certificate using the crt file.  
   git config --global http.sslCAInfo c:\Users\svc_tfs17_app\ca-bundle.crt
3. Convert the \n (Unix) to \r\n (Windows) so that it can be displayed by notepad editor correctly.
4. Use the unix2dos open source software to convert \n to \r\n, or other notepad editor to replace \n to \r\n.

Here's the download link: http://www.efgh.com/software/unix2dos.htm

5. Copy the content of tfs.cer from step o to ca-bundle.crt at the bottom of the file.

Done!

Output :

Fix for SSIS not picking correct configuration file

When there are more then one configuration file for a single package, the SSIS jobs sometimes do not automatically pick the correct configuration depending on how the settings has been made. 

In the SSIS package (using Visual Studio) Make sure you've done following correctly:

1) In the package property, Set Delay Validation =  false
2) Uncheck , Enable Package Configuration. 


steps for 2):

a) open the ssis package in visual studio.

b) In the Control flow tab, right click on the blank space of the canvas.

c) go to Package Configurations...

d) uncheck Enable Package Configuartion and press Close.

e) Save the ssis package and copy that to the deployed folder.