Authenticating with Microsoft SQL Server

During DataHub configuration, you must select an authentication mode for the Database Engine. There are two possible options: Integrated authentication also known as Windows authentication and Use specific credentials, which implements SQL authentication. Choosing an authorization mode depends on many factors that are unique to each implementation and environment. When in doubt, utilize Integrated authentication.

Integrated Authentication (Windows Authentication)

Selecting Integrated Authentication, credential information is not required to be entered as DataHub will utilize the previously configured Windows Service Account.

  • The person who is configuring DataHub must use the same user name for the DataHub service and the MS SQL server authentication
  • When DataHub connects through a Windows user account, SQL Server validates the Service Account name and password using the Windows principal token in the operating system
  • This means that the user identity is confirmed by Windows. SQL Server does not ask for the password, and does not perform the identity validation.  

When choosing Integrated authentication, the Windows Account specified in the DataHub service configuration must have "DB Creator" role on the SQL Server.

  • Alternatively, you can pre-create a SQL Server database and grant the Windows Account "DB Owner" role.

It is recommended to utilize Integrated authentication wherever possible. Integrated authentication uses a series of encrypted messages to authenticate users in SQL Server. When SQL Server logins are used when the "Use specific credentials" (SQL Authentication) mode is selected, SQL Server login names and encrypted passwords are passed across the network, which makes them less secure.

Use Specific Credentials (SQL Server Authentication)

When using SQL Server Authentication, logins are created in SQL Server that are not based on Windows user accounts. Both the user name and the password are created by using SQL Server and stored in SQL Server. Users connecting using SQL Server Authentication must provide their credentials (login and password) every time that they connect. When using SQL Server Authentication, you must set strong passwords for all SQL Server accounts.

The Use specific credentials (SQL Authentication) mode, is most often utilized in scenarios such as:

  • Connections from non-Windows environments (i.e. DataHub Install on Linux connecting to SQL Server)
  • Connections from untrusted domains

Related Links