Desktop productivity for business analysts and programmers

Using ODBC on Enterprise Guide

Reply
Contributor
Posts: 29

Using ODBC on Enterprise Guide

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

Grand Advisor
Posts: 9,596

Re: Using ODBC on Enterprise Guide

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

Respected Advisor
Posts: 3,066

Re: Using ODBC on Enterprise Guide

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;


Valued Guide
Posts: 2,111

Re: Using ODBC on Enterprise Guide

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.

Ask a Question
Discussion stats
  • 3 replies
  • 2422 views
  • 0 likes
  • 4 in conversation