SAS Access to MSSQL on linux through Windows AD ID

[Debi]

Hi there,

We want to access windows SQL Server (MSSQL) from SAS on unix using windows AD ID as login credentials.

We think for that we nbeed to set up SAS Access to MSSQL and also ODBC Datadirect. 

So,could someone help me on these??

1. How can I achieve this and perform the set up being the SAS admin.

2. IF SAS/ Access to MSSQL software needed to be installed,then what would be its cost.

3. Do we need ODBC Datadirect  for this? If yes, then how we can install, configure it in association with SAS Access to MS SQL.

Any other helpful suggestions would be great..

Thanks,

Debi

[Timmy2383]

Debi,

I could be wrong, but I'm pretty sure you can't use AD login when coming from SAS on Linux (unless maybe you have IWA setup on the servers). You ca use Windows Authentication to the databases when coming from a SAS session running in Windows, but when coming from a Limux platform you have to use database authentication. So an account has to be created and given server level and database level access (typically done by the DBAs). Usually service IDs are created for this purpose and then an authdomain with the credentials is created in metadata, associated with some sort of metadata group used for database access (at least according to some best practices).

1. SAS Access to ODBC and SAS Access to MSSQL Server are two different products, technically, but they can be installed and set up using the deployment wizard. You don't really need both, you can use ODBC for MSSQL server databases, though probably would need Acess to MSSQL server for more advanced features specific to MSSQL server (if there are any).

2. No idea about cost. You'd have to talk to your sale rep. I know that when I dealt with datadirect in the past (with a 9.3 installation) they charged several thousand per core, but in my current installation it came bundled since we licensed it as part of the contract.

3. Technically you could use other products like EasySoft, which is probably cheaper, but DataDirect partners with SAS and can be installed and configured with the deployment wizard.

Usually once it is installed you have to set an environment variable for the installation path in SAS setenv_local file. Then you modify the ODBC.ini file fore your data sources.

Check out pages 57-58 of this document:
http://support.sas.com/documentation/installcenter/en/ikfdtnunxcg/66380/PDF/default/config.pdf#page6...

[Debi]

Hi Timmy2383,

Thanks for sharing your thoughts. I guess I couldn't put my points properly. Restating my questions here::

What I understand by following different sites is; by installing Data Direct ; we can login through windows AD on SQLSERVER.

So, I wanted to know how is it possible(i mean steps) and how mush it will cost for SAS/Acceess to MSSQL and DataDirect products(licensed one) .

Do you have any thoughts on this.

Thanks,

Debi

[Timmy2383]

DataDirect provides the ODBC driver manager and specific drivers.  It provides the capability for SAS in a Unix/Linux environment to communicate with the MSSQL server databases.  Authentication to the database is a separate issue.  You can't use Windows AD (Windows NT authentication) to log into the database from a Unix/Linux environment (it might be possible if you IWA configured on your Linux servers).  Usually you have to use SQL Server authentication, which means the DBAs have to set up a separate account for the user (or a service account) with its own password and everything.  Basically, the account is only known to the database, it's not an AD account.  You would then use this account in your libname statements, whether explicitly or through an authdomain.

I would list the steps for setup as follows:

1. Install and configure SAS/Access to ODBC or MSSQL Server with SAS Deployment Wizard (assuming you have it licensed for your site and it is included in your plan file).
2.  Modify the <sashome>/bin/setenv_local file to include the paths to the DataDirect installation and odbc.ini file
3. Add a data source to the DataDirect odbc.ini file
4. Have the DBAs create an account on the database with the access you need, and provide you with the account name and password.
5. Test the connection with a libname in SAS. Maybe something like:

libname test ODBC datasrc=mydatabase schema='dbo' user='userid' password='thepassword';

In this example "mydatabase" would just be whatever you called the data source entry in your odbc.ini file.

Again, I can't speak to actual cost. You would need to talk a DataDirect sales rep or your site's SAS sales rep.

[SASKiwi]

It looks like operating system authentication is possible used Kerboros as explained here:

http://support.sas.com/kb/42/605.html

This is tricky to set up so I'm hoping someone else might have checked this out.

We use SAS/ACCESS to ODBC to connect to SQL Server from SAS. However all our servers use Windows so IWA is easy to set up without using Kerberos.

[JuanS_OCS]

Hi,

I am wondering: are you currently able to connect to SAS on Linux with your IWA credentials (your current windows/ AD credentials)?

If not, I would suggest to configure this first (with PAM, as a good example), and the kerberos delegation (SPNs, the keypass file, etc), then you can move forward to configure the driver as indicated by @SASKiwi. Otherwise I doubt the SQL Server sill ever be able to get the AD credentials.

If yes, then you are on the right path from my point of view, and you can move forward to this configuration.

Besides, most part of the steps, are explained here: http://www.easysoft.com/products/data_access/odbc-sql-server-driver/kerberos.html

Regarding cost and licenses, we cannot help you, sorry.