MSSQL comes in many forms, versions, and with many authentication methods.
Most of the time we end up using the regular MSSQL authentication, where we provide a username/password combination, the user gets authenticated in the DB and there we go.
Many other times we need a more complex authentication method. Integrated Security, NTLM, Windows Authentication, Named Instances, Domains, etc. All those parameters can affect the way we need to login to MSSQL Database when we use AI/Spoon to connect.
The first one is the most used one, "MS SQL Server"
This one uses the 1.2.5 JTDS driver.
This driver is good for most common cases, but if you want to use Windows Authentication, Integrated Security or Named Instances you may run into problems.
You can use the JTDS FAQ (jTDS JDBC Driver) to answer the most common questions about this driver. Even some troubleshooting of the most common errors thrown.
I personally recommend you download the latest JTDS driver from here: jTDS - SQL Server and Sybase JDBC driver download | SourceForge.net
As of this writing it is 1.3.1. If you do not have this version you may encounter some issues connecting to MSSQL 2012 or higher, even if you are using the most basic of authentications.
If you want to use Integrated Security (also called Active Directory authentication, or AD) you can do so here as well, or even use Domain/User authentication. I will explain how each one works.
If you are not familiar with AD, its a centralized authentication mechanism allowing access to the various hardware and services in the network. By centralizing the authentication process, the same user account can be used to access multiple resources, and it eliminates some of the setup needed to enable those users on various systems. Most DBA’s prefer to use AD authentication for those reasons, and if you will be using PDI to access multiple MSSQL systems, you’ll probably want to become familiar with setting it up.
- Although Microsoft provides their own JDBC driver, which we will cover later on this post, this time around we will be using the open source driver jTDS.
- Extract the archive file and open it. Copy the jtds-1.3.1.jar file to the Pentaho ...ARSystem/diserver/data-integration/libext/JDBC (In version 9.1SP2 or older) or ...ARSystem/diserver/data-integration/lib (9.1SP3 or newer) folder on your system. Remove the jtds-1.2.5.jar file from there.
- In the folder where you extract the archive, locate the subfolder matching your systems architecture (x64, x86 or IA64). Open it, and open the SSO subfolder.
- Copy the ntlmauth.dll file to a folder on your path. (From a command prompt enter: ECHO %PATH% (In windows) or ECHO $PATH$ (In Linux) to see the current path). On my system, I copied the file (as root) to the /usr/local/bin folder. In windows a good location could be /Windows/system32/
- Open Spoon.
- Right click on Database Connections, and choose NEW to open the Database Connection window.
- Enter a name in the Connection Name box to identify it.
- Scroll down in Connection Type and choose MS SQL Server.
- In the Access panel, make sure Native (JDBC) is selected.
- In the Settings panel, enter your server’s hostname or IP address, the database you want to connect to, the port SQL Server is using (by default its 1433), and the user name and password in the appropriate fields. You can leave Instance Name empty unless your DBA tells you the server is using a named instance.
- In the left most panel, select Options. The right panel will refresh, and will probably only have one value entered: “instance”. Leave the value as is.
- Only in cases where you want the actual WindowsUser account that started Spoon to be the one authenticated add a parameter called “integratedSecurity” (watch the text case), and set the value to true. If this is true then you do not need to provide a Username and Password combination. Windows will use the current user that started Spoon to authenticate to the DB. This is most of the times NOT a good option, since for ex. you may need to connect to two different DBs, with two different domain/user combinations. If so, do not use this parameter.
- Add another parameter called “domain” and set the value to your network’s domain name. (You can use the full domain name or the shorthand one).
- Click the TEST button at the bottom of the screen, and you should be rewarded with a successful connection window. Click OK and you are done.
Note: You may get the message of
The login is from an untrusted domain and cannot be used with Windows authentication. (code 18452, state 28000)
Some SQL Server machines are forced to work exclusively with NTLMv2 authentication. When attempting to create an account with SQL Server using Windows Authentication, its validation will fail with untrusted user/domain error.
In order to verify which authentication is used, execute gpedit.msc on the SQL Server host and look at the selected value of Computer Configuration->Windows Settings->Security Settings->Local Policies->Security Options->Network Security: LAN Manager
(ie. Authentication Level is set to "Send NTLMv2 response only. Refuse LM & NTLM" )
The default value for useNTLMv2 is false, so we need to set it to true to send LMv2/NTLMv2 responses when using Windows/Domain authentication.
We need to set the parameter "useNTLMv2" to "true" under "domain" (step 13 above).
Note also that if you decide to go with Integrated Security, the username/password combination will be the one that actually run the service that tries to connect to the DB. In this case, it is Spoon, but in the case you run the job from the AI Console the connection will be triggered/established by the Carte Server. The Carte server is a process that runs under the ARSystem (as a child of it) and it is started and monitored by ArMonitor. It's starting line is in the armonitor.cfg/conf file and unless you do something about it, CarteServer will be started by the same user that starts the ARServer, which means that this user may fail to connect even though you tested the job from Spoon and it was working.
For the above I do NOT recommend using Integrated Security unless really needed.
Now to the second option: MS SQL Server (Native)
You normally would use this option for better performance and for using the official Microsoft supported JDBC connection. This JDBC driver is the most compatible driver in the market, and it's officially supported by the same company that releases new MSSQL releases, so it makes sense that any new versions of MSSQL DB Server may be accompanied by new JDBC releases as well. In case your JTDS driver above stops working you may have to default to this one for new DB releases.
To deploy/install this you need to:
1. Download and install the JDBC 4.0, 6.0, 6.2 or any other from Microsoft official webpage. Download Microsoft JDBC Driver for SQL Server - SQL Server | Microsoft Docs.
2. After download install so that the files get unzipped to one folder. Then go to the folder and grab sqljdbc4.jar, copy this file to <AtriumIntegrator folder>/data-integration/libext/JDBC/ (In versions up to 9.1SP2) or to <AtriumIntegrator folder>/data-integration/lib/ (In versions 9.1SP3 or greater).
3. Do the same with the sqljdbc_auth.dll file from the 64 bits folder (unless you are on a 32 bit system). Copy that file to <AtriumIntegrator folder>/data-integration/libswt/
4. Open Spoon, create a new DB Connection and use the MS SQL Server (native) option.
On the left hand side of the connection properties page, go to options. Add one property: integratedSecurity and set the value to true. In the user name type the user in the format Domain\UserName and the password. Or otherwise use the property Domain in the options tab.