Setup with SQL2012 - Native SSPI library not loaded

Hi,

I am trying to configure TeamCity to use a SQL 2012 database and am having some difficulties.

I have been able to create a database and use the migration tool to populate the SQL database using windows authentication without any problems.

When I start the service the teamcity-service.log file indicated that the Native SSPS library cannot be found:

[2014-01-06 17:36:22,672]   INFO -  jetbrains.buildServer.STARTUP - ===========================================================

[2014-01-06 17:36:22,672]   INFO -  jetbrains.buildServer.STARTUP - Starting TeamCity server

[2014-01-06 17:36:22,844]   INFO -  jetbrains.buildServer.STARTUP - TeamCity version: 8.0.5 (build 27692), data format version 628

[2014-01-06 17:36:22,844]   INFO -  jetbrains.buildServer.STARTUP - OS: Windows Server 2008 R2, JRE: 1.7.0_21-b11

[2014-01-06 17:36:22,844]   INFO -  jetbrains.buildServer.STARTUP - Servlet real path: D:\TeamCity\bin\..\webapps\ROOT\

[2014-01-06 17:36:22,860]   INFO -  jetbrains.buildServer.STARTUP - Current stage: Looking for the TeamCity Data Directory

[2014-01-06 17:36:22,860]   INFO -  jetbrains.buildServer.STARTUP - TeamCity Data Directory path is determined using the environment variable TEAMCITY_DATA_PATH

[2014-01-06 17:36:22,860]   INFO -  jetbrains.buildServer.STARTUP - Data Directory "D:\ProgramData\JetBrains\TeamCity" exists

[2014-01-06 17:36:22,922]   INFO -  jetbrains.buildServer.STARTUP - The main configuration file "D:\ProgramData\JetBrains\TeamCity\config\main-config.xml" exists

[2014-01-06 17:36:22,922]   INFO -  jetbrains.buildServer.STARTUP - The database properties file "D:\ProgramData\JetBrains\TeamCity\config\database.properties" exists

[2014-01-06 17:36:22,922]   INFO -  jetbrains.buildServer.STARTUP - The internal database data file "D:\ProgramData\JetBrains\TeamCity\system\buildserver.data" exists

[2014-01-06 17:36:22,985]   INFO -  jetbrains.buildServer.STARTUP - Data Directory version: 628

[2014-01-06 17:36:22,985]   INFO -  jetbrains.buildServer.STARTUP - Current stage: Looking for the database configuration

[2014-01-06 17:36:22,985]   INFO -  jetbrains.buildServer.STARTUP - Database properties file "D:\ProgramData\JetBrains\TeamCity\config\database.properties" exists

[2014-01-06 17:36:22,985]   INFO -  jetbrains.buildServer.STARTUP - Internal HSQL database file (D:\ProgramData\JetBrains\TeamCity\system\buildserver.data) exists, version: 2.2.9

[2014-01-06 17:36:23,000]   INFO -  jetbrains.buildServer.STARTUP - Database connection URL: jdbc:jtds:sqlserver://DenSql01A:1433/TeamCity;integratedSecurity=true

[2014-01-06 17:36:23,000]   INFO -  jetbrains.buildServer.STARTUP - Using database connection URL from the database properties file. The URL is: jdbc:jtds:sqlserver://DenSql01A:1433/TeamCity;integratedSecurity=true

[2014-01-06 17:36:23,000]   INFO -  jetbrains.buildServer.STARTUP - Using external (MSSQL) database

[2014-01-06 17:36:23,000]   INFO -  jetbrains.buildServer.STARTUP - Current stage: Connecting to the database

[2014-01-06 17:36:23,313]  ERROR -  jetbrains.buildServer.STARTUP - SQL error when doing: Connecting to MSSQL: I/O Error: SSO Failed: Native SSPI library not loaded. Check the java.library.path system property.

[2014-01-06 17:36:23,313]   INFO -  jetbrains.buildServer.STARTUP - Current stage: TeamCity server startup error

[2014-01-06 17:36:23,313]   INFO -  jetbrains.buildServer.STARTUP - Administrator login is required from web UI using authentication token:



The database.properties file contains:
# This is a sample configuration file for configuring TeamCity to use external database.
# To make it effective, copy it to the "database.properties" file and modify settings
# according to your environment.
# Do not modify this file, it will be overwritten on TeamCity server startup.
# See documentation at http://www.jetbrains.net/confluence/display/TCD8/Setting+up+an+External+Database

# Database: Microsoft SQL server (via jtds driver)   

connectionUrl=jdbc:jtds:sqlserver://DenSql01A:1433/TeamCity;integratedSecurity=true

# Note: following two properties are *not* used in case of Integrated Authentication.
#connectionProperties.user=<username>
#connectionProperties.password=<password>

# The maximum number of connections TeamCity can open on database server.
maxConnections=50

# Specifies whether TeamCity should check each jdbc connection before use.
# It reduces the system performace but avoids cases when the system uses
# a connection that was closed by database server due to time out or this
# connection was broken at the previous operation.
testOnBorrow=true


I am using TeamCity (version 8) and the 1.2.8 of the jTDS driver.

I have copied the ntlmauth.dll into:
D:\TeamCity\Bin
D:\TeamCity\jre\bin
C:\Program Files\Java\Jre7\Lib

In frustration I've also put the file into the C:\Windows\System32 directory.

I've looked at existing posts which state to copy the ntlmauth file to the Bin directory but that doesn't seem to work for me.  I'm sure the file needs to be put into a different directory I'm just not sure where!

Any help would be appreciated and sorry if the solution is there, I just couldn't find it!

Many thanks,

Graham
3 comments
Comment actions Permalink

Hello Graham,

Please, try to do the following:

  1. Use the native driver instead of JTDS. Here are instructions: http://confluence.jetbrains.com/display/TCD8/Setting+up+an+External+Database#SettingupanExternalDatabase-MicrosoftSQLServer section "Native driver".
  2. Ensure that you're using SSPI driver with the same bitness as of the JRE. You can use 32-bit SSPI driver with 32-bit JRE or 64-bit SSPI driver with 64-bit JRE. See here how to specify JRE for TeamCity server: http://confluence.jetbrains.com/display/TCD8/Installing+and+Configuring+the+TeamCity+Server#InstallingandConfiguringtheTeamCityServer-JavaInstallation
  3. Ensure that there are no other SSPI drivers in all directories listed in the PATH variable and in the Windows/System32 and Windows/SysWow64.


Hope it helps.

Please, let me know did it help. Thanks.

0
Comment actions Permalink

Hi Leonid,

Thank you for your reply, I greatly appreciate it.

I have followed the steps and finally got it working, I think the problem was that I installed a newer version of the Java Runtime and hadn't updated the version in the D:\TeamCity\Jre\Bin directory.



Steps I followed:

First, I removed the jTDS driver:

Removed the ntlmauth.dll from
•D:\TeamCity\bin
•D:\TeamCity\jre\bin

I then delete the files in: D:\ProgramData\JetBrains\TeamCity\lib\jdbc (these were the files extracted from the jTDS driver).

I then downloaded the native driver from here:  http://www.microsoft.com/download/en/details.aspx?displaylang=en&id=11774
(I downloaded the exe - version 4.0)

Unpacked the driver to the D drive of the server (D:\Microsoft JDBC Driver 4.0 for SQL Server)

I copied the D:\Microsoft JDBC Driver 4.0 for SQL Server\sqljdbc_4.0\enu\sqljdbc4.jar to the D:\ProgramData\JetBrains\TeamCity\lib\jdbc directory.

Copied the file D:\Microsoft JDBC Driver 4.0 for SQL Server\sqljdbc_4.0\enu\auth\x64\sqljdbc_auth.dl to C:\Windows\System32 directory.

I then amended the database.properties file (in the D:\ProgramData\JetBrains\TeamCity\config directory):

# This is a sample configuration file for configuring TeamCity to use external database.
# To make it effective, copy it to the "database.properties" file and modify settings
# according to your environment.
# Do not modify this file, it will be overwritten on TeamCity server startup.
# See documentation at http://www.jetbrains.net/confluence/display/TCD8/Setting+up+an+External+Database
# Database: Microsoft SQL server (via jtds driver)
connectionUrl=jdbc:sqlserver://DenSql01A:1433;databaseName=TeamCity;integratedSecurity=true
# Note: following two properties are *not* used in case of Integrated Authentication.
# GR 06/01/2013 - Commented out the lines below to use Windows Authentication
#
#connectionProperties.user=<username>
#connectionProperties.password=<password>
# The maximum number of connections TeamCity can open on database server.
maxConnections=50
# Specifies whether TeamCity should check each jdbc connection before use.
# It reduces the system performace but avoids cases when the system uses
# a connection that was closed by database server due to time out or this
# connection was broken at the previous operation.
testOnBorrow=true

At this point I got an error stating:

[2014-01-07 17:14:29,979]  ERROR -  jetbrains.buildServer.STARTUP - SQL error when doing: Connecting to MSSQL: This driver is not configured for integrated authentication. ClientConnectionId:dd4a5441-7909-4441-8086-703f67ae301b

I then noticed your comment about how to specify the Java version and the link associated with it.

Specificly the comment on the page:

if your TeamCity installation has bundled JRE (there is <TeamCity home>\jre directory), update it by installing newer JRE per installation instructions and copying the content of the resulting directory to replace the content of the existing <TeamCity home>\jre directory). If you also run TeamCity agent from <TeamCity home>\buildAgent directory, use JVM installation instead of JRE.

I then copied the Lib and Bin directory from C:\Program Files\Java\jre7 to D:\TeamCity\jre

Upon starting the service this worked.

I think the previous driver would have worked if I had updated the JRE file in the D:\TeamCity\jre folder.

Thank you once again for your help.

Graham

0
Comment actions Permalink

Graham, thank you for details!

JTDS driver works with SSPI too but sometimes it requires more effort to configure.


Don't hesitate to contact me on further questions!

--
Leonid Bushuev

0

Please sign in to leave a comment.