topic Re: MS Sql connection in SAS Viya4 in Administration and Deployment

MS Sql connection in SAS Viya4

ravikumar901 — Thu, 22 Aug 2024 17:05:30 GMT

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.

Re: MS Sql connection in SAS Viya4

Sajid01 — Thu, 22 Aug 2024 20:04:49 GMT

- 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-interface-in-sas-viya-4
Though it is for Oracle, the concepts apply to your case. You will find answers to your questions.

Re: MS Sql connection in SAS Viya4

ravikumar901 — Tue, 27 Aug 2024 12:32:06 GMT

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 !

Re: MS Sql connection in SAS Viya4

gwootton — Wed, 28 Aug 2024 13:38:46 GMT

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

Re: MS Sql connection in SAS Viya4

Sajid01 — Wed, 28 Aug 2024 13:49:15 GMT

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.