How to connect to Azure SQL Managed Instance using Azure Service Principal from SAS EG

[pragam21]

Hello everyone,

I'm currently trying to upload data from SAS 9.4 to Azure SQL Managed Instance. I have the following information about the destination:

Instance Name - ABC.XYZ.database.windows.net

Client ID - abcd

Client Secret - XXXXX

Schema - MNOP

I need to use Service Principal authentication-based access to the Azure SQL Managed Instance. I've used Libname statement to connect to the target DB but I'm getting error while connecting.

Please let me know if I need any other information to connect. Also, please help me with the libname statement over here.

[LinusH]

What error are you getting?

If you unsure what it means issue

options msglevel=i sastrace='d,,d' sastraceloc=saslog nostsuffix;

before the libname statement.

Assuming the fire wall is open.

Have you tried the connection from a non-SAS client?

[pragam21]

When I'm trying the below LIBNAME statement, it's throwing the CLI error as the connection cannot be established.

Spoiler

LIBNAME MYAZURE ODBC PROMPT=YES DATAsrc="ABC.XYZ.database.windows.net" SCHEMA ="MNOP" USER="abcd" PWD="XXXX";

---

 And, when I'm trying the following LIBNAME statement, it's saying that "Invalid option name SERVER"

LIBNAME MYAZURE ODBC PROMPT=YES SERVER="ABC.XYZ.database.windows.net" DATABASE= "MNOP" USER="abcd" PWD="XXXX";

Also, I haven't tried the connection from a Non SAS client.

[SASKiwi]

Since you are using ODBC you could test out the connection settings in the Windows ODBC administrator tool on your PC. Check first to see you have an SQL Server driver installed first:

With an SQL Server driver installed like above you can then create a User DSN to test a database connection. If you can get it working on your PC then you can try the same settings in SAS. If SAS is running on the Windows Server OS then you can remotely logon to it and create the same DSN connection in the ODBC Administrator tool. If SAS is running on linux then connection details need to be defined in odbc.ini and that is a job for your SAS administrator. If SAS is running on your PC then you can use the DSN you already tested.