topic Re: SAS Viya connection to SQL Server in Administration and Deployment

SAS Viya connection to SQL Server

telligent — Fri, 30 Sep 2022 20:33:58 GMT

We have SAS Interface to ODBC installed, will we be able to access SQL Server or do we need SAS Interface to MS SQL Server? I am not sure if these are two separate products and two separate ways of connecting or if one is a subset of the other. We are on windows machines. If we can connect using SAS Interface to ODBC, could you direct me to a link with instructions on how to connect. Thank you.

Re: SAS Viya connection to SQL Server

SASKiwi — Fri, 30 Sep 2022 21:52:42 GMT

ODBC is fine for MS SQL Server. With SAS Viya, database connections need to be setup by your SAS administrator on the SAS servers. What version of Viya are you using? Viya 4 is linux only.

Re: SAS Viya connection to SQL Server

telligent — Mon, 03 Oct 2022 23:09:27 GMT

We have Release 5.2. Can you direct me to instructions, our administrators don't know how to it.

Re: SAS Viya connection to SQL Server

SASKiwi — Tue, 04 Oct 2022 00:00:32 GMT

I'm not aware of any Viya release 5.2. There is Viya 3.5 (and some older 3.x releases) and Viya 4, now named 202n.n. Also what OS does it run on? I'm guessing linux. Also which SAS/ACCESS product are you using? ODBC or SQL Server? It would be most likely quicker to open a Tech Support track for this so TS can walk your administrators through the process.

Re: SAS Viya connection to SQL Server

gwootton — Tue, 04 Oct 2022 13:09:37 GMT

5.2 is probably your version of SAS Studio rather than Viya. I believe SAS Studio 5.2 is on Viya 3.5.

Here is the documentation on the ODBC driver for Microsoft SQL Server:
https://learn.microsoft.com/en-us/sql/connect/odbc/microsoft-odbc-driver-for-sql-server?view=sql-server-ver16

Here is the Viya 3.5 Administration documentation after you've configured the ODBC driver:

Viya 3.5 Administration - Configure SAS/ACCESS Interface to ODBC (Linux)
https://go.documentation.sas.com/doc/en/calcdc/3.5/dplyml0phy0lax/p03m8khzllmphsn17iubdbx6fjpq.htm#p1qkm4koo8isxyn1xgb55165fgq5

Re: SAS Viya connection to SQL Server

telligent — Tue, 11 Oct 2022 17:09:02 GMT

How do I find or get to these folders from within SAS Studio? Or outside SAS Studio?

/opt/sas/viya/config/etc/workspaceserver/default/workspaceserver_usermods.sh
/opt/sas/viya/config/etc/sysconfig/compsrv/default/sas-compsrv
sudo vi /opt/sas/viya/config/etc/cas/default/cas_usermods.settings

Re: SAS Viya connection to SQL Server

SASKiwi — Tue, 11 Oct 2022 19:05:23 GMT

The best way is to remotely logon to a SAS Compute server session so you can use OS commands. You will need administrator rights to do this. Typical SAS users will not normally have these permissions.

If your SAS administrators don't know how to configure a Viya SQL Server connection they should open a SAS Tech Support track so they can be guided through the process.

Re: SAS Viya connection to SQL Server

telligent — Tue, 11 Oct 2022 21:35:00 GMT

Our SAS Administrator does not know how to do this and he did call tech support and spoke with Grant, who I am told was not much help. Perhaps if I gave you the ticket number, you could help explain the level of support that we need. He just gave us the same information that you posted above.

Re: SAS Viya connection to SQL Server

SASKiwi — Tue, 11 Oct 2022 23:43:36 GMT

I'm not in SAS Tech Support, I'm just a SAS customer who likes helping others for free, so I don't have access to TS tracks. If you are getting insufficient help, I suggest you escalate this through your management to your SAS Account Manager to get a better response. In my experience SAS TS is excellent, and if they are unable to help themselves, will escalate a track to someone who can.

Re: SAS Viya connection to SQL Server

telligent — Wed, 12 Oct 2022 00:47:12 GMT

Appreciate your help. My experience with TS has also been good, but I am not the one calling in this case. Will take your advice to escalate. Thank you.