Libname issue when connecting to SQL Server

[RJ2016]

I am using SAS Enterprise guide 7.1 (SAS 9.04) I am trying to connect using my user details for example i have to stipulate domain name when connecting to Sql server

(replaced my real username with example)

libname testc sqlsvr dsn=Customer_details user=asd\E99999 password=12345678. schema='dbo'; <-- this does not work as it sas doesnt like the \ however i need to include the domain name asd otherwise i cant connect to sql server, is there a way to get around this issue

error message

ERROR: CLI error trying to establish connection: [SAS][ODBC SQL Server Wire Protocol driver][SQL Server]Login failed for user
       'asd\E99999'.
ERROR: Error in the LIBNAME statement.

[JBailey]

Hi @RJ2016

Is this something that has worked in the past and is now failing?

There should be no problem with the '\' in the username. Perhaps, it could be that the account that you are using does not have permissions in the dbo schema or that there is another permissions problem with your account. One way to test this is to connect to the database using a non-SAS query tool - preferably one that ships with SQL Server (your DBA may be able to help with this). At a minimum try to connect via the ODBC Admin tool. 

If you want to try something via SAS remove the SCHEMA= option. 

Good luck.

Best wishes,

Jeff

[RJ2016]

Hi,

The connection works fine outside of SAS

however when we upgraded, im not sure if we still have generic SAS/ACCESS to ODBC driver. that maybe the issue?

I can work around issue when testing a service account credentials for SQL Server so I might just have to get the company create a service account so I don't have to use a domain\ user login.

[JBailey]

Hi @RJ2016

Was this working and then stopped?

When you test, are you positive that the ODBC DSNs connecting to the same data source?

Best wishes,

Jeff

[RJ2016]

Hi, yes the dns are connecting to same connection source

Before the sas upgrade we used to connect using ODBC in the libname statement however when upgrading we use SQLsvr instead in the statement as ODBC no longer works (which was advised would happen after upgrade)

for example

if I libname

libname testc sqlsvr dsn=Customer_details user=Area_test password=12345678. schema='dbo'; < then this works as its a service account (using SQL Server Authentication within SQL Server)

libname testc sqlsvr dsn=Customer_details user=dsaz\my_user_name  password=12345678. schema='dbo'; < then this does not work  (using Windows Authentication within SQL Server)

[JBailey · Accepted solution]

Hi @RJ2016

I think I have your answer...

I was able to reproduce your error. I fixed it by setting AuthenticationMethod=4 (Kerberos) in the ODBC configuration. Here is what it looks like in ODBC Manager.

My LIBNAME statement looks like this:

libname lib1 sqlsvr dsn=MS_SQL_WIN_AUTH user=mydom\myuserid password="MyPassword1" database=mydb;

It is important to note that the above LIBNAME statement worked with both quoted, and unquoted, strings for USER=.

You can also do this with a DSN-less LIBNAME statement. I like these because I am lazy and don't like going through the ODBC Administrator.

libname mssql sqlsvr complete="DRIVER={SAS ACCESS to SQL Server};
HOST='sqlserverdb.mycompany.com';DB=mydb;PORT=1433;
AuthenticationMethod=4;
EncryptionMethod=0;" 
user=mydom\myuserid password='MyPassword1!';

Hopefully this will help you.

Best wishes,

Jeff

[Tom]

Normally if the username or password value contains special characters you need enclose them in quotes so that SAS can parse the code.

user='asd\E99999' password='12345678'

[JBailey]

Hi @Tom

All I can say is that my LIBNAME statement worked with, and without, quoting.

Best wishes,

Jeff