Connect SAS Viya (Pay-as-you-go) to Azure SQL Server

[Clark]

There is very little documentation that I can find on how to connect the Azure marketplace SAS Viya (Pay-as-you-go) with the Azure SQL within the same subscription.

Please could someone share the steps required.

[eastChase]

I am stuck on this too, did you solve this issue? @rich_sas

[Clark]

Hello @eastChase,

We were able to connect using the following:

1. Enable SQL Server Authentication on the database. Noting to keep the DB credentials safe.

2. Create the odbc.ini file within /export/<path>/data/db on the NFS server.

3. Within the standard variables of the odbc.ini, we found that these worked:

AuthenticationMethod=1
EncryptionMethod=1
ValidateServerCertificate=0

4. Update the odbc.ini variable in the Kubernetes ConfigMap so that SAS knows about the new odbc.ini file that is on the JUMP server.

kubectl -n <name> edit configmap sas-access-config-<uid>

This will open up an editor to map change to the configMap.

5. This may take a few minutes to take effect.

I hope this helps.

[priyhat_sas]

Below are the steps for connecting to Azure SQL server:

1. In the odbc.ini file that is located in the jump vm of the deployment (/viya-share/access-clients/odbc/odbc.ini), under section [ODBC Data Sources] section, add the bold font for Azure Synapse. Then add the block for Azure synapse [SQL Server Wire Protocol - Azure Synapse/SSL Enabled Example].

[ODBC Data Sources]
SQL Server Legacy Wire Protocol=SAS ACCESS to MS SQL Server Legacy
SQL Server Wire Protocol=SAS ACCESS to MS SQL Server
Amazon RedShift Wire Protocol=SAS ACCESS to Amazon Redshift
Greenplum Wire Protocol=SAS ACCESS to Greenplum
SQL Server Wire Protocol - Azure Synapse/SSL Enabled Example=SAS ACCESS to MS SQL Server

[SQL Server Wire Protocol - Azure Synapse/SSL Enabled Example]
Driver=/opt/sas/viya/home/lib64/accessclients/lib/S0sqls28.so
Description=SAS Institute, Inc 8.0 SQL Server Wire Protocol
AEKeyCacheTTL=-1
AEKeystorePrincipalId=
AEKeystoreClientSecret=
AlternateServers=
AlwaysReportTriggerResults=0
AnsiNPW=1
ApplicationIntent=0
ApplicationName=
ApplicationUsingThreads=1
AuthenticationMethod=1
BulkBinaryThreshold=32
BulkCharacterThreshold=-1
BulkLoadBatchSize=1024
BulkLoadFieldDelimiter=
BulkLoadOptions=2
BulkLoadRecordDelimiter=
BulkLoadThreshold=2
ColumnEncryption=Disabled
ConnectionReset=0
ConnectionRetryCount=0
ConnectionRetryDelay=3
CryptoProtocolVersion=TLSV1,TLSV1.1,TLSV1.2
Database=
Domain=
EnableBulkLoad=0
EnableQuotedIdentifiers=0
EnableServerSideCursors=1
EnableScrollableCursors=4
EncryptionMethod=1
FailoverGranularity=0
FailoverMode=0
FailoverPreconnect=0
FetchTSWTZasTimestamp=0
FetchTWFSasTime=1
GSSClient=native
HostName=<SQL_Server_host>
HostNameInCertificate=
#IANAAppCodePage=
InitializationString=
KeepAlive=0
Language=
LoadBalanceTimeout=0
LoadBalancing=0
LoginTimeout=15
LogonID=
MaxPoolSize=100
MinPoolSize=0
MultiSubnetFailover=0
PacketSize=-1
Pooling=0
PortNumber=<SQL_Server_server_port>
PRNGSeedFile=/dev/random
PRNGSeedSource=0
ProxyHost=
ProxyMode=0
ProxyPassword=
ProxyPort=
ProxyUser=
QueryTimeout=0
ReportCodepageConversionErrors=0
SnapshotSerializable=0
TrustStore=
TrustStorePassword=
ValidateServerCertificate=1
WorkStationID=
XMLDescribeType=-10
SSLLibName=/usr/lib64/libssl.so.10
CryptoLibName=/usr/lib64/libcrypto.so.10

2. In Azure portal for SQL server, make sql db accesible to all azure services as shown below. 

3. Make sure there is a sql database exists in the SQL server created, else create a new database. 

4. In the libname statement, use these options:

SSLLibName=/usr/lib64/libssl.so.1.1;
CryptoLibName=/usr/lib64/libcrypto.so.1.1;

libname paygsql sqlsvr noprompt="DRIVER=SAS ACCESS to MS SQL Server;
HOST=mysqlserver.database.windows.net;
PORT=1433;
database=sqlsdbname;
UID=user;
PWD=pwd;
SSLLibName=/usr/lib64/libssl.so.1.1;
CryptoLibName=/usr/lib64/libcrypto.so.1.1;";

With these steps, the connection to Azure SQL server is successful. Please let us know if you have further questions.