SAS Interface/ACCESS to MS SQL Server

[rico_ehrlich]

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

[jakarman]

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).aspxMicrosoft 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.a...

- 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).  

[OS2Rules]

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.

[dpage]

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

[Doc_Duke]

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

[rico_ehrlich]

SAS 9.4 M1

[rico_ehrlich]

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