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.
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
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
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.
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
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)
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
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'
Hi @Tom
All I can say is that my LIBNAME statement worked with, and without, quoting.
Best wishes,
Jeff
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.
Find more tutorials on the SAS Users YouTube channel.