MS Sql connection in SAS Viya4

[ravikumar901]

In the SAS documents , its showing MSSQL connection can be established by odbc driver and odbc is installed by default in sas deployment. I have used Use Kustomize PatchTransformers to attach the storage with your configuration files to SAS Viya. Within the $deploy/sas-bases/examples/data-access directory,  in these example files i have configured mount paths i.e, data-mounts-cas.sample.yaml, data-mounts-deployment.sample.yaml, and data-mounts-job.sample.yaml
and configured env variables in sas-access.properties but here when im trying to establish connection of  MSSQL datasource in CAS im getting following issue


ERROR: ERROR: [DataDirect][ODBC lib] Driver Manager Message file not found. Please check for the value of InstallDir in your odbc.ini 

- Do we need to install ODBC drivers in Viya accessclients mount path

or need to configure host ,port and DSN of MS SQL in odbc.ini

- Do we need to open port of MSSQL datbase to k8s cluster nodes of sas viya

Please suggest me here. Thanks in advance.

[Sajid01]

- Do we need to install ODBC drivers in Viya access clients mount path?
Yes MSSQL odbc client driver  for 64 bit Linux would be needed (unless it is built in to Viya 4)

- Do we need to open port of MSSQL database to k8s cluster nodes of sas viya?
well the Viya4 environment and the SQL server should be able to connect to each other.
You can contact SAS Tech Supports to get  assistance appropriate to your case.
Have a look at this video
https://video.sas.com/detail/video/6234732546001/sas-demo-%7C-how-to-configure-a-sas-access-interfac...
Though it is for Oracle, the concepts apply to your case. You will find answers to your questions.

[ravikumar901]

how can we set odbc.ini in Viya4 ? we are not  passing odbci.ini file from deployment assets , do we need to set this in pods ?
Please help me out. Thanks in advance !

[Sajid01]

One can reference the details in $deploy/sas-bases/examples/data-access/README.md for your installation.
The ODBC clients/drive need to be installed in a Persistent Volume storage. 

One should typically be able to access with ssh. There must be a jump box to access this.
I would prefer creating a separate folder for each ODBC driver

Select a driver that could be extracted and used (tar.gz extension) rather that that needs installation (rpm)
The video I referred to above and the readme file as above are helpful and the process pretty straight forward.

[gwootton]

The MS SQL ODBC driver should be present by default, and can use DSN-less connections so you wouldn't need an ODBC file but you could put one in the mount you're adding with the data-mounts sample transformers. If you are setting a DSN when configuring the the connection in Viya, you'd probably need to provide the odbc file.
For connections from CAS, there would need to be a network path from the CAS node to the MS SQL server, so if that port is not opened it would need to be.

Verify SAS/ACCESS Interface to Microsoft SQL Server
https://go.documentation.sas.com/doc/en/sasadmincdc/v_055/calvalid/p0455nqweukxh3n1gommqk2sdpaf.htm