topic Re: Using ODBC on Enterprise Guide in SAS Enterprise Guide

Using ODBC on Enterprise Guide

Jchapman — Fri, 18 Jul 2014 16:07:40 GMT

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

Re: Using ODBC on Enterprise Guide

Ksharp — Sat, 19 Jul 2014 13:01:35 GMT

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

Re: Using ODBC on Enterprise Guide

SASKiwi — Sun, 20 Jul 2014 19:53:04 GMT

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;

Re: Using ODBC on Enterprise Guide

Doc_Duke — Mon, 21 Jul 2014 13:04:13 GMT

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.