05-03-2016 06:13 AM
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..
05-03-2016 07:34 AM
05-03-2016 09:03 AM
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.
05-03-2016 09:24 AM
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.
05-04-2016 12:46 AM
It looks like operating system authentication is possible used Kerboros as explained here:
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.
05-04-2016 04:41 AM
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.