topic Re: SAS Interface/ACCESS to MS SQL Server in Administration and Deployment

SAS Interface/ACCESS to MS SQL Server

rico_ehrlich — Mon, 06 Oct 2014 06:59:39 GMT

Hi all,

I have installed the unixODBC 2.3.0 and MS SQL Server ODBC Driver msodbcsql-11.0.2270.0. Then I have installed SAS Interface/ACCESS to MS SQL Server.

ODBC.INI:

[mfbs_db]

Description = SQL Server Database

Driver = ODBC Driver 11 for SQL Server

Servername = MFBSSQLP01/XRM

Port = 59714

Trace = Yes

Database = MFBS_MSCRM

ODBCINST.INI:

[ODBC Driver 11 for SQL Server]

Description = Microsoft ODBC Driver 11 for SQL Server

Driver = /opt/odbc_mssql/msodbcsql-11.0.2270.0/lib64/libmsodbcsql-11.0.so.2270.0

Threading = 1

$LD_LIBRARY_PATH:

/opt/odbc_unix:/opt/odbc_unix/lib:/opt/odbc_unix/lib64:/opt/odbc_mssql/msodbcsql-11.0.2270.0/lib:/opt/odbc_mssql/msodbcsql-11.0.2270.0/lib64:/o

pt/sas/SASHome/Secure/sasexe:/opt/sas/SASHome/SASPrivateJavaRuntimeEnvironment/9.4/jre/lib/amd64/server:/opt/sas/SASHome/SASPrivateJavaRuntimeE

nvironment/9.4/jre/lib/amd64:/opt/sas/SASHome/SASFoundation/9.4/sasexe

$ODBCINI:

/etc/odbc.ini

$ODBCSYSINI:

/etc

libname-statement:

libname mydblib sqlsvr

noprompt="uid=<user>;

pwd={SAS002}F560034D1111E5B200B1C5553A6A67744BE8CBB1;

dsn=mfbs_db;"

stringdates=yes;

Error-Message:

ERROR: CLI error trying to establish connection: [unixODBC][Driver Manager]Data source name not found, and no default driver specified

ERROR: Error in the LIBNAME statement.

Can anyone help me with this issue? What I am doing wrong in configuration?

Regards,

Christoph

Re: SAS Interface/ACCESS to MS SQL Server

jakarman — Mon, 06 Oct 2014 10:42:23 GMT

You have a lot of settings mentioned. What is not mentioned:

Are these settings active in the SAS session that is trying to connect?

As you are mentioning a Unix approach you are most likely using Eguide and a WS-server.

These settings should get activated using SAS configuration files. It does not help when you are doing that in your personal Unix profile settings

When you are using the system profiles for modifications than those can be revoked and reset by your OS system administrator.

You probably are following: Installing the Driver Manager (http://technet.microsoft.com/en-us/library/hh568449(v=sql.110).aspx Microsoft ODBC Driver for SQL Server as of MS)

The message is telling you the name mfbs_db is not found.

Troubleshooting:

- http://blogs.msdn.com/b/sql_protocols/archive/2008/04/30/steps-to-troubleshoot-connectivity-issues.aspx

- unixodbc manual: http://www.unixodbc.org/doc/UserManual/ is using a graphical interface. You should be able to use a X-server terminal on your desktop.

When the datamanager is working proceed to your SAS interface

Having installed the ODBC driver you should check the functionality of that one outside of SAS.

Do not trust the pwencode approach. Your userid is not secret you could have left to the real one in your SAS example code.

See: https://communities.sas.com/thread/61088

Having a demo/check account for just verifying the technical connections is very sensible advice. You can validate that always without worrying to do something with real possible sensitive business production information (data).

Re: SAS Interface/ACCESS to MS SQL Server

OS2Rules — Mon, 06 Oct 2014 12:37:33 GMT

Also - you need to check that the port number is set on the ODBC as defined in your code. We found that even with the port

number in the code, it still had to be set manually. Also - make sure you have the appropriate authority to define the ODBC.

Re: SAS Interface/ACCESS to MS SQL Server

dpage — Mon, 06 Oct 2014 14:23:43 GMT

I've always had to set ODBCHOME as well.

http://support.sas.com/documentation/installcenter/en/ikfdtnunxcg/66380/PDF/default/config.pdf

And to verify SAS always pick it up (for all users), set it - and all the other required variables in !SASROOT/bin/sasenv_local

Re: SAS Interface/ACCESS to MS SQL Server

Doc_Duke — Tue, 07 Oct 2014 15:01:53 GMT

What version of SAS? We had to update SAS 9.4 to M2 to get the ODBC & SQLSVR to work together.

Re: SAS Interface/ACCESS to MS SQL Server

rico_ehrlich — Tue, 07 Oct 2014 15:04:36 GMT

SAS 9.4 M1

Re: SAS Interface/ACCESS to MS SQL Server

rico_ehrlich — Tue, 14 Oct 2014 14:09:21 GMT

it is working now with sql server authentification. now the last task is to get this running with kerberos and windows authentification via AD.

thanks for your help and support.

regards,

christoph