Setup of staging TeamCity Server

Permanently deleted user

Created September 23, 2015 00:30

Hi..
I am working on setting up staging Teamcity Server with similar h/w configuration.I have empty MSSQL DB and tried to import the backup file (which has the files and DB contents) from the primary server and getting following error.

Primary server TC v7.1.5 and uses MS SQL server as external DB.
I have confirmed that user has the dbo access on DB and removed the DB name / user and password from the following output.

Please let me know, if I am missing any step.

Using logs directory e:\Data\Servers\TeamCity-Server\bin\..\logs
TeamCity maintenance tool. Copyright 2011 JetBrains s.r.o. All Rights Reserved.

Command line arguments: restore -A e:\Data\Servers\TeamCity-7.1.5-Config -F e:\D
ata\Servers\tmp\TeamCity_Backup_20150921_235033.zip -T e:\Data\Servers\tc-upgrad
e\database.properties.mig
Using TeamCity data directory: e:\Data\Servers\TeamCity-7.1.5-Config
Restoring from backup file: e:\Data\Servers\tmp\TeamCity_Backup_20150921_235033.
zip
Backup created by TeamCity version:
        Version: 7.1.5
        Build number: 24400
        Data format version: 544
Using explicitly specified target database settings: e:\Data\Servers\tc-upgrade\
database.properties.mig
Using target database
        database type: MSSQL
        database driver: <not specified>
        connection: jdbc:sqlserver:<ServerName>;databaseName
=teamcitydb_mig;user=<username>;password=<passwd>
        login as: jirasrvsql
Connected to the Target DB successfully.
Failed: Unexpected exception SQLServerException: SQL error when doing: Querying
for list of values
SQL query: select name
from sys.tables
where schema_id = schema_id()
  and type = 'U'
SQL exception: The SELECT permission was denied on the object 'tables', database
'mssqlsystemresource', schema 'sys'.
jetbrains.buildServer.serverSide.db.UnexpectedDBException: Unexpected exception
SQLServerException: SQL error when doing: Querying for list of values
SQL query: select name
from sys.tables
where schema_id = schema_id()
  and type = 'U'
SQL exception: The SELECT permission was denied on the object 'tables', database
'mssqlsystemresource', schema 'sys'.
        at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError
(SQLServerException.java:216)
        at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServ
erStatement.java:1515)
        at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePrep
aredStatement(SQLServerPreparedStatement.java:404)
        at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtExecC
md.doExecute(SQLServerPreparedStatement.java:350)
        at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:5696)
        at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLSe
rverConnection.java:1715)
        at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLSer
verStatement.java:180)
        at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLS
erverStatement.java:155)
        at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeQuery(
SQLServerPreparedStatement.java:285)
        at jetbrains.buildServer.serverSide.db.DBFunctions.executeQuery(DBFuncti
ons.java:499)
        at jetbrains.buildServer.serverSide.db.DBFunctions.queryForListOfValues(
DBFunctions.java:474)
        at jetbrains.buildServer.serverSide.db.DBFunctions.queryForStrings(DBFun
ctions.java:425)
        at jetbrains.buildServer.serverSide.db.DBFunctionsForMSSQL.retrieveSchem
aTableNames(DBFunctionsForMSSQL.java:149)
        at jetbrains.buildServer.serverSide.maintenance.BackupRestoreRunner.chec
kTargetDatabaseIsEmpty(BackupRestoreRunner.java:1016)
        at jetbrains.buildServer.serverSide.maintenance.BackupRestoreRunner.main
Main(BackupRestoreRunner.java:255)
        at jetbrains.buildServer.serverSide.maintenance.BackupRestoreRunner.main
(BackupRestoreRunner.java:207)
Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: The SELECT permissio
n was denied on the object 'tables', database 'mssqlsystemresource', schema 'sys
'.
        ... 16 more
Done.

8 comments

Alina Mishina

Created September 24, 2015 15:45

Hi Rajan,

The database connection URL should have the following format:

connectionUrl=jdbc:sqlserver://<host>\\<instance_name>:1433;databaseName=<database_name>

without username and password. Username and password should be specified using properties:

connectionProperties.user=<user>
connectionProperties.password=<password>

Please check that database.properties file is configured correctly. Also pease double-check that the user account used by TeamCity has permissions to create new, modify and delete existing tables in its schema, in addition to usual read/write permissions on all tables.

Also we would recommend to upgrade to the latest TeamCity version (9.1.2 as of now) to get access to all the recent bug fixes and lots of new features.

Permanently deleted user

Created September 25, 2015 20:32

Hi Alina,
I have updated the database properties file and getting the same error.  I have checked with internal DBA team and confirmed that user has 'dbo' role. Infact DBA gave all permissions for teh teamcity user.

I am trying to setup a staging server which will be used to test the upgrade to the latest TC server version.

I am just curious about "empty" DB requirement. Is there any additional requirement for the schema or some tables ?

Connected to the Target DB successfully.
Failed: Unexpected exception SQLServerException: SQL error when doing: Querying
for list of values
SQL query: select name
from sys.tables
where schema_id = schema_id()
  and type = 'U'
SQL exception: The SELECT permission was denied on the object 'tables', database
'mssqlsystemresource', schema 'sys'.
jetbrains.buildServer.serverSide.db.UnexpectedDBException: Unexpected exception
SQLServerException: SQL error when doing: Querying for list of values
SQL query: select name
from sys.tables
where schema_id = schema_id()
  and type = 'U'
SQL exception: The SELECT permission was denied on the object 'tables', database
'mssqlsystemresource', schema 'sys'.

I am not a DB expert but why error is pointing to database 'mssqlsystemresource' ?

Thanks ..
Raviraj.

Permanently deleted user

Created September 25, 2015 22:49

Hi Alina

ok. DBA team was able to drop and create the empty DB again and provided the dbo role. I was able to restore the backup file and it completed successfully.

last couple of lines from the console running restore process.

Importing Build Logs
Finishing
Restored 56097 files.
Restoring finished successfully
Changing default database connection in TeamCity configuration:
Copying target database properties file
        from:   e:\Data\Servers\tc-upgrade\database.properties.mig
        to:     e:\Data\Servers\TeamCity-7.1.5-Config\config\database.properties

Done.

Started the TC server service and tried to login .. getting following error on the webpage.

      <pre>java.lang.IllegalStateException: @NotNull method jetbrains/buildServer/serverSide/db/jdbcLoader/JdbcDriverLoader.listJdbcDriverJars must not return null
at jetbrains.buildServer.serverSide.db.jdbcLoader.JdbcDriverLoader.listJdbcDriverJars(JdbcDriverLoader.java:60)
at jetbrains.buildServer.serverSide.db.jdbcLoader.JdbcDriverLoader.loadJdbcDrivers(JdbcDriverLoader.java:40)
at jetbrains.buildServer.maintenance.StartupProcessor.performLoadJdbc(StartupProcessor.java:349)
at jetbrains.buildServer.maintenance.StartupProcessor.performConnectToDatabase(StartupProcessor.java:24)
at jetbrains.buildServer.maintenance.StartupProcessor.doDatabaseConnect(StartupProcessor.java:87)
at jetbrains.buildServer.maintenance.StartupProcessor.processConcreteStage(StartupProcessor.java:22)
at jetbrains.buildServer.maintenance.StartupProcessor.processConcreteStageSafe(StartupProcessor.java:20)
at jetbrains.buildServer.maintenance.StartupProcessor.processTeamCityLifecycle(StartupProcessor.java:378)
at jetbrains.buildServer.maintenance.StartupProcessor.access$000(StartupProcessor.java:507)
at jetbrains.buildServer.maintenance.StartupProcessor$1.run(StartupProcessor.java:1)
at java.lang.Thread.run(Unknown Source)
</pre>

Current Startup State

Startup status

Current step: TeamCity server startup error
Next step: not defined yet

Data Directory

Directory path: E:\Data\Servers\TeamCity-7.1.5-Config exists
Database properties file exists
Internal database file not found

Database

Database type: MSSQL
Database connection URL: jdbc:sqlserver://xxx;databaseName=xxx
Not connected to the database yet.

Versions

Software version: 544
Data directory version: 544
Database version: unknown

Logs

Logs path: E:\Data\Servers\TeamCity-Server\bin\..\logs

TeamCity 7.1.5 (build 24400)
------------------------------------------------------------------------------------------------------------------------

Can you please check and let me know, if you need any additional information.

Thanks ..
Rajan.

Alina Mishina

Created September 26, 2015 16:56

Hi Rajan,

Please attach teamcity-server.log file covering the time of startup.

Permanently deleted user

Created September 27, 2015 00:09

Hi Alina
Please see attached Teamcity server log.

Attachment(s):
teamcity-server.log.zip

Alina Mishina

Created September 29, 2015 22:44

Hi Rajan,

Sorry for delay. It seems that JDBC driver cannot be loaded successfully. Please download and replace current JDBC driver as described here.

Permanently deleted user

Created October 01, 2015 21:04

HI Alina
I tried downloaded JDBC driver with the staging server but still getting the same error.

I tried the jdbc drivers from existing PROD server (where it's working) and still getting the same error.

Alina Mishina

Created October 05, 2015 13:11

It looks strange. Did you download the MS sqljdbc package from [http://www.microsoft.com/download/en/details.aspx?displaylang=en&id=11774], unpack it and copy the sqljdbc4.jar from the just downloaded package into the

<TeamCity Data Directory>/lib/jdbc

directory? Also please check that the user you run TeamCity under has permissions to see/open this file.

Please sign in to leave a comment.