BookmarkSubscribeRSS Feed
Jchapman
Calcite | Level 5

I am connecting to the server on a client side installation of Enterprise Guide. How do I properly configure ODBC and/or SQL Server and/or SAS to allow a connection through ODBC to a SQL Server using Integrated Windows Authentication/NT Authentication?

I receive the following error:

ERROR: CLI error trying to establish connection: [Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user 'NT

       AUTHORITY\ANONYMOUS LOGON'.

Thank you,

James

3 REPLIES 3
Ksharp
Super User

If your OS is windows, you should firstly setup the SQLServer ODBC driver , technically it have already been setup in windows . then open Data source(ODBC) under manager tools . make a connection with wizard and don't forget to test if it is connecting successfully . Open SAS write your libname odbc .

Xia Keshan

SASKiwi
PROC Star

Here is a typical example or how we do it. This approach avoids having to configure data sources in the Windows ODBS administrator tool which is a major advantage. The Trusted Connection=yes string does the Windows authentification.

BTW the NOPROMPT option should be typed exactly as follows without spaces, otherwise it probably won't work.

Also we find it convenient to define all of SQL Server libnames in SAS metadata so they are automatically defined when you open a server connection from EG. If you wish to do this add the DEFER = YES option to libnames are only activated when referenced which improves EG connection performance.

libname test odbc noprompt = "server=MySQLServer;DRIVER=SQL Server;Trusted Connection=yes" QUALIFIER = MyDatabase schema = MySchema readbuff = 32767;


Doc_Duke
Rhodochrosite | Level 12

One additional thought.  Some DBAs disable 'trusted connections' for SQL/Server.  If you can't use it interactively, you can't use it with SAS.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

SAS Enterprise Guide vs. SAS Studio

What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 3 replies
  • 5526 views
  • 0 likes
  • 4 in conversation