Windows authentication
info
Windows authentication is available for Microsoft SQL Server connections.
DataFactory can sign in to SQL Server with an Active Directory account (Windows authentication, over Kerberos) instead of a SQL login. It works everywhere a connection is used: testing, schema scans, prunes, Bloom learning from a source and Bloom loads.
Nothing is installed or configured on the DataFactory server. Each connection holds its own AD account and either its password or a keytab, stored encrypted like any saved password. DataFactory gets and renews the Kerberos tickets itself.
Before you start
Ask your Active Directory and database administrators for:
| What's needed | |
|---|---|
| An AD account | A service account for DataFactory, e.g. svc_datafactory. AES encryption enabled on the account. |
| A SQL Server login for it | CREATE LOGIN [CORP\svc_datafactory] FROM WINDOWS, with read access for Prune, or write access on test databases Bloom loads into. |
| An SPN for SQL Server | MSSQLSvc/<server FQDN>:<port> registered on SQL Server's service account. Most installations already have one. |
| DNS | The DataFactory server must resolve the SQL Server's full name. To find domain controllers automatically it uses the _kerberos._tcp.<realm> DNS record; otherwise list them on the connection. |
| Network | From DataFactory: port 88 (TCP) to the domain controllers, and the SQL Server port. |
| Time | The DataFactory server's clock within 5 minutes of the domain's. Use the same NTP source. |
| A keytab (optional) | Instead of saving the password, export a keytab for this one account, e.g. with ktpass. Export it again after every password change. |
Set up the connection
- Add connection → Microsoft SQL Server, and connect by Host. (Windows authentication isn't available with URL.)
- Enter the server's full name (FQDN), such as
sql01.corp.example.com, not its IP address. - Under Authentication, set Method to Windows (Kerberos).
- Enter the Realm: the AD domain in capitals, e.g.
CORP.EXAMPLE.COM. - Enter the Username:
svc_datafactory,svc_datafactory@CORP.EXAMPLE.COMandCORP\svc_datafactoryall work. - Choose Sign in with:
- Password: enter the account's password and turn on Save password, or
- Keytab file: click Upload keytab and choose the file.
- Optional, under Advanced:
- Server SPN, if SQL Server's SPN isn't the default, or you must connect by IP address. It must name the same server, e.g.
MSSQLSvc/sql01.corp.example.com:1433. The connection's realm is added if you leave it off; add@OTHER.REALMfor a server in another realm. - KDC servers, if DNS can't find the domain controllers: up to 10, comma separated, as
hostorhost:port. If your admin has setCONNECTION_ALLOWED_HOSTS, these must be allowed hosts too.
- Server SPN, if SQL Server's SPN isn't the default, or you must connect by IP address. It must name the same server, e.g.
- Click Save connection.
View details then shows Authentication: Windows (Kerberos), the realm, SPN and KDC servers, and Keytab on server: Stored (not shown).
note
Everyone who uses this connection signs in to SQL Server as this one AD account, not as themselves. People you share a data set with don't see the account's realm, SPN or keytab.
Coming from Enov8 TDM
Copy the values from your TDM connection.
| TDM field | DataFactory field |
|---|---|
| Server | Host (use the FQDN) |
| Database | Database |
| Port | Port |
| Schema | Chosen later, when you build a virtual data set |
| Authentication Mechanism: Windows Authentication or GSSAPI | Method: Windows (Kerberos) |
| Username | Username |
| Realm | Realm |
| Password | Sign in with: Password |
| Keytab Path | Sign in with: Keytab file, then upload the file |
| Kinit Path | Not needed |
| Server SPN | Advanced: Server SPN |
DataFactory doesn't need the Kerberos packages, systemd service or Apache settings that TDM uses.
Troubleshooting
| Message | What to do |
|---|---|
| Wrong password for svc@REALM. | Correct the password. |
| The keytab doesn't match the current password of svc@REALM. Export a new keytab. | The account's password changed. Export and upload a new keytab. |
| The keytab has no key for svc@REALM. | The keytab is for another account. Export one for this account. |
| Account svc wasn't found in realm REALM. | Check the account name and realm. |
| Account svc@REALM is locked, disabled or expired in Active Directory. | Ask your AD administrator. |
| The password for svc@REALM has expired. Change it in Active Directory, then save it here. | Change it in AD, then edit the connection. |
| No Kerberos server (KDC) was found for realm REALM… | Check the realm and the _kerberos._tcp DNS record, or enter KDC servers. |
| Can't reach a Kerberos server (KDC) for realm REALM on port 88… | Open port 88 from DataFactory to the domain controllers. |
| This server's clock and the domain's differ by more than 5 minutes… | Sync both to the same NTP source. |
| No SPN MSSQLSvc/host:port is registered in Active Directory… | Connect with the server's FQDN, register the SPN, or set Server SPN. |
| Windows authentication needs the server's name (FQDN), not an IP address…, or Use the server's name, or set Server SPN under Advanced. | Enter the FQDN, or set Server SPN under Advanced. |
| SQL Server accepted the Windows sign-in but has no login for svc… | Ask the DBA to create the login. |
| … Data Factory waits 10 minutes before trying these details again, so the account isn't locked out. | After a wrong or locked account, DataFactory pauses so it doesn't lock the AD account. Correct the details to try straight away. |
Limits
- SQL Server only, connected by host and port.
- One AD account per connection. DataFactory doesn't pass through the signed-in person's identity, and doesn't use delegation.
- A saved password or keytab is needed for prunes and loads that run without you.