topic Re: LIBNAME SQLSVR with Personal Login Manager server info in SAS Enterprise Guide

LIBNAME SQLSVR with Personal Login Manager server info

SVoldrich — Thu, 03 Oct 2024 14:28:38 GMT

team is using:

SAS EG 8.2

Personal Login Manager 9.4
I inherited this code, and it works on the old server.

Our server is updating in the next week, so I'm going through all our queries to make sure they are pointed to the correct new server. (New server is live now, so we can connect. work is still being done in old server until final cutover and then new server will have the most current data.)

This query connects through a libname.
LIBNAME COBSPROD SQLSVR authdomain=SQLSVR database=COBS SCHEMA=DBO;

It's been a while since I've had to set up a libname for sql server.

I can't figure out how/where to check it's connected to the correct server.

Re: LIBNAME SQLSVR with Personal Login Manager server info

ChrisNZ — Sat, 05 Oct 2024 19:56:50 GMT

You can extract information to validate what's there, like:

proc sql;
  connect using COBSPROD;
  select * from connection to COBSPROD (select * from  sys.sysdatabases);
quit;

Extract the information that will make you confident you are using the correct server.

Re: LIBNAME SQLSVR with Personal Login Manager server info

ChrisNZ — Sat, 05 Oct 2024 19:59:19 GMT

You can also execute commands such as:

execute by COBSPROD (drop table test_table1) ;

Re: LIBNAME SQLSVR with Personal Login Manager server info

SVoldrich — Mon, 07 Oct 2024 12:09:50 GMT

Thanks.
Right now the old server is still up (we are confirming that things are working in the new server) so the 2 servers should be identical.
Just trying to know how to know which server it's connected to.

is it based on our ODBC connection?

Re: LIBNAME SQLSVR with Personal Login Manager server info

ChrisNZ — Mon, 07 Oct 2024 12:37:38 GMT

Are you using ODBC? I'd expect a dsn option in the libname statement if you are.

That ODBC manager's DSN entry would contain the ODBC connection parameters.

I'd just create a test table on the new server and check that I can see it.

Re: LIBNAME SQLSVR with Personal Login Manager server info

AlanC — Mon, 07 Oct 2024 14:26:15 GMT

I would echo ChrisNZ's suggestion and use ODBC. In pretty much every MS/SAS consulting client, I would always encourage ODBC. It just works and is easier to configure.

OleDB is similar but ODBC is more widely used. I don't believe I used the SQLSRVR engine and I did dozens of SAS/SQL Server engagements. IIRC, it was difficult to get setup with modern SQL Server engines due to security. I am working off of memories so it certainly may have changed.

Re: LIBNAME SQLSVR with Personal Login Manager server info

SASKiwi — Mon, 07 Oct 2024 22:52:10 GMT

Looks like your LIBNAME is using an AUTHDOMAIN to define the credentials to connect to SQL Server. Ask your SAS administrator to explain the setup. This is done in SAS metadata via SAS Management Console.

Re: LIBNAME SQLSVR with Personal Login Manager server info

ChrisNZ — Mon, 14 Oct 2024 15:53:38 GMT

Please select a reply as an answer if you answered your question.

Re: LIBNAME SQLSVR with Personal Login Manager server info

Sajid01 — Thu, 17 Oct 2024 15:45:24 GMT

Hello
Your server appears to be connecting to the server defined by authdomain=SQLSVR . Your SAS Admin should be able to provide the details.
You can try a direct libname statement to connect to the new server. This link has the details.

https://documentation.sas.com/doc/en/pgmsascdc/9.4_3.5/acreldb/n0gz66qe8msnkyn1jgnhrr0l2k8y.htm