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.
... View more