Streamlined SSO Setup: Connecting IBM SPSS Modeler to MS SQL with Active Directory Integration
In this article (cowritten with Harshavardhan Changappa), we will guide you through the process of establishing a Single Sign-On (SSO) connection to an MS SQL database in SPSS Modeler Client. Before diving into the detailed procedure, let’s understand some related terminologies.
Key Terminologies
Single Sign-On (SSO): Single Sign-On allows user to access multiple applications with a single set of login credentials. For instance, after logging into computer or network with username and password, user can open SPSS Modeler and connect to a SQL database without re-entering login details. SPSS Modeler uses the fact that user are already authenticated to grant access.
Active Directory (AD): Active Directory acts like an organized phonebook for computer network. It tracks all users, computers, and other resources, managing their usernames and passwords. With SSO, credentials used to connect to the network are centrally managed by AD.
Windows Integrated Authentication for MS SQL: This authentication method uses Windows account credentials to log into a SQL Server. Instead of entering separate SQL Server login details, it automatically use the same credentials used to log into Windows computer.
IBM SPSS Modeler is a data mining and predictive analytics software that enables users to build and deploy predictive models from large datasets.
To Know more about IBM SPSS Modeler: https://www.ibm.com/docs/en/spss-modeler/18.5.0
Steps to Connect an AD User to MS SQL in SPSS Modeler Client/Server
With a basic understanding of the necessary terms, let’s proceed to the setup.
a. Set Up Active Directory:
Set up AD on a new machine. Kindly refer this blog for detail steps on
setting up the Active Directory.
b. Configure Machines to the Same AD Domain:
Ensure all relevant machines (where SPSS Modeler is installed, where the SPSS Modeler server is running, and where the MS SQL server is running) are configured to the same Active Directory domain. Refer to the same document mentioned in ‘step a’ for detailed configuration instructions.
c. Add the AD user as a Login to the MS SQL server:
· Open Open SQL Server Management Studio (SSMS) and connect to the server instance by passing the server credentials which is set during installation of SQL.
· In Object Explorer, expand the Security node.
· Right-click on Logins and select New Login.
· In the ‘Login — New’ window, click ‘Search’.
· Select ‘Entire Directory’ and enter the AD user or group name, then click ‘Check Names’.
· Once the AD user or group is found, click ‘OK’.
· Configure the appropriate server roles and database access for the new login.
· Click ‘OK’ to create the login.
d. In the machine where Modeler server is running, create a DSN for SQL Server with Windows Integrated Authentication:
· Open ODBC Data Source Administrator.
· Click on the System DSN tab. Click Add to create a new DSN.
· From the list of drivers, select ODBC Driver 17 for SQL Server. If it is not in the list then check the ODBC driver installation.
Note: ‘SQL Server’, ‘ODBC Driver 17 for SQL Server’ or ‘ODBC Driver 18 for SQL Server’ provide the option for ‘Windows Integrated Authentication’.
· Click Finish.
· Enter a name for DSN. With this DSN it will be referred in SPSS Modeler.
· Enter the name or IP address of SQL Server. Click Next.
· Select With Integrated Windows authentication. This tells the ODBC driver to use Windows credentials.And click on Next.
· Select the default database you want to connect to. Click Next.
· Configure any additional options as needed. Typically, these fields are at their default values and click Finish.
· Click the Test Data Source button to ensure the DSN is configured correctly and can connect to the SQL Server.
· If the test is successful, click OK to save the DSN.
Note: While testing database connection, ensure you are logged into the machine with the same AD user which is added as login to the MS SQL Server in ‘step c’.
e. Connect to the DSN in SPSS Modeler:
· Open SPSS Modeler logging to the machine with AD user.
· Connect to the Modeler Server by providing AD user credentials.
Server name should be in this form — <hostname>.<AD domain> for eg: c12345v1.abc.xyz.com
· In the “Sources” tab, drag a “Database” node onto the canvas.
· Double-click the “Database” node to configure it.
· Select <Add new database connection…> from the Data Source dropdown.
· In the ‘Database Connection’ list, select the DSN which is created for SQL connection.
· Since the DSN uses Windows Integrated Authentication, SPSS Modeler should connect to the SQL Server using Windows credentials.
· Click Connect to verify the connection.
Now, SPSS Modeler should be able to use the ODBC DSN to connect to the SQL Server using Windows Integrated Authentication.
Security Benefits
Windows Integrated Authentication enhances security when accessing a SQL database through SPSS Modeler
· Windows Integrated Authentication leverages Kerberos or NTLM protocols, which are more secure than basic authentication methods. These protocols provide encrypted and secure verification of user credentials. NTLM protocol is used in the above setup. Kerberos requires additional configuration.
· Since users don’t have to enter their database credentials separately, the risk of passwords being exposed or shared insecurely is minimized.
Summary
This article provides a detailed explanation of the steps required to set up a secure connection to an MS SQL database in SPSS Modeler. By leveraging SSO and Windows Integrated Authentication with an Active Directory user, user can ensure a secure and streamlined connection process.
