[Other doc versions]
[Doc downloads (PDF, HTML, XML)]
This page describes how to connect Stash to a Microsoft SQL Server database.
The overall process for using a SQL Server database with Stash is:
It is assumed here that you already have SQL Server installed and running.
See Supported platforms for the versions of SQL Server supported by Stash.
If you are migrating your data from the internal Stash database, back up the Stash home directory.
If you are migrating your Stash data from a different external database, back up that database by following the instructions provided by the database vendor before proceeding with these instructions.
See Data recovery and backups.
Before you can use Stash with SQL Server, you must set up SQL Server as follows:
Step | Notes |
Create a database | e.g. stash . Remember this database name for the connection step below. |
Set the collation type | This should be case-sensitive, for example, 'SQL_Latin1_General_CP1_CS_AS' (CS = Case Sensitive). |
Set the isolation level | Configure the database to use the isolation level, Read Committed with Row Versioning. |
Create a database user | e.g. stashuser . This database user should not be the database owner, but should be in the db_owner role. See SQL Server Startup Errors. Remember this database user name for the connection step below. |
Set database user permissions | The Stash database user has permission to connect to the database, and to create and drop tables, indexes and other constraints, and insert and delete data, in the newly-created database. |
Enable TCP/IP | Ensure that TCP/IP is enabled on SQL Server and that SQL Server is listening on the correct port (which is 1433 for a default SQL Server installation). Remember this port number for the connection step below. |
Check the authentication mode | Ensure that SQL Server is operating in the appropriate authentication mode. By default, SQL Server operates in 'Windows Authentication Mode'. However, if your user is not associated with a trusted SQL connection, 'Microsoft SQL Server, Error: 18452' is received during Stash startup, and you will need to change the authentication mode to 'Mixed Authentication Mode'. Stash instances running on Windows are also able to support SQL Server databases running in 'Windows Authentication Mode'. This is described at the bottom of this page and it has to be manually configured: Connecting Stash to SQL Server - Use Integrated Authentication (Optional) |
Check that SET NOCOUNT is off | Ensure that the SET NOCOUNT option is turned off. You can do that in SQL Server Management Studio as follows:
|
Note that Stash will generally require about 25–30 connections to the database.
Note also that Stash requires the datebase to keep idle connections alive for at least 10 minutes. If the database is configured with less than a 10 minute connection timeout, there will be seemingly random connection errors.
Here is an example of how to create and configure the SQL Server database from the command line. When Stash and SQL Server run on the same physical computer (accessible through localhost
), run the following commands (replacing stashuser
and password
with your own values):
SQL Server> CREATE DATABASE stash SQL Server> GO SQL Server> USE stash SQL Server> GO SQL Server> ALTER DATABASE stash SET ALLOW_SNAPSHOT_ISOLATION ON SQL Server> GO SQL Server> ALTER DATABASE stash SET READ_COMMITTED_SNAPSHOT ON SQL Server> GO SQL Server> ALTER DATABASE stash COLLATE SQL_Latin1_General_CP1_CS_AS SQL Server> GO SQL Server> SET NOCOUNT OFF SQL Server> GO SQL Server> USE master SQL Server> GO SQL Server> CREATE LOGIN stashuser WITH PASSWORD=N'password', DEFAULT_DATABASE=stash, CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF SQL Server> GO SQL Server> ALTER AUTHORIZATION ON DATABASE::stash TO stashuser SQL Server> GO
This creates an empty SQL Server database with the name stash
, and a user that can log in from the host that Stash is running on who has full access to the newly created database. In particular, the user should be allowed to create and drop tables, indexes and other constraints.
You can now connect Stash to the SQL Server database, either:
Hostname | The host name or IP address of the computer running the database server. |
Port | The TCP port with which Stash can connect to the database server. The default value of 1433 is the default port that SQL Server runs against. You can change that if you know the port that your SQL Server instance is using. |
Database name | The name of the database that Stash should connect to. |
Database username | The username that Stash should use to access the database. |
Database password | The password that Stash should use to access the database. |
Named Instances
If you have a named instance on your server, you will need to manually edit the stash-config.properties
file as described on the Connecting to named instances in SQL Server from Stash Knowledge Base article.
Windows authentication is only available for Stash instances running on Windows. It cannot be used on Linux because Microsoft does not provide shared objects for it. You will either need to run Stash on Windows, allowing you to use Windows security, or you will need to enable mixed-mode authentication for SQL Server if you are running Stash on Linux. Unfortunately, there are no other options at this time.
Integrated authentication uses a native DLL to access the credentials of the logged-in user to authenticate with SQL Server. The native DLLs for both 32- and 64-bit systems are included in the distribution; there is no need to download the entire package from Microsoft.
Stash does not currently support configuring the system to use integrated authentication from the UI (Vote for it! - STASH-3035Getting issue details... STATUS ). This means you can't currently migrate to SQL Server with integrated authentication, nor can you configure Stash to use SQL Server with integrated authentication during initial setup. However, if Stash has already been configured to use SQL Server (for example, when the Setup Wizard was run at first use), you can enable integrated authentication by directly modifying Stash's configuration, as follows:
x64
or x86
DLL to sqljdbc_auth.dll
in lib/native
. Note that running on Windows x64 does not require the use of the x64
DLL; you should only use the x64
DLL if you are also using a 64-bit JVM.setenv.bat
, a JVM_LIBRARY_PATH
variable has already been defined. Simply remove the leading rem
. Note that if you are putting the native DLL in an alternative location, you may need to change the value to point to your own path. The value of the JVM_LIBRARY_PATH
variable will automatically be included in the command line when Tomcat is run using start-stash.bat
.%STASH_HOME%\stash-config.properties
file to include ;integratedSecurity=true
in the jdbc.url
line. Note that jdbc.user
and jdbc.password
will no longer be used to supply credentials but they must still be defined – Stash will to fail to start if these properties are removed.It is also possible to configure integrated authentication over Kerberos, rather than using the native DLLs. Details for that are included in the JDBC documentation.
This section is only relevant to some distributions of Stash, for example if you are running Stash via the Atlassian Plugin SDK, or have built Stash from source.
If the SQL Server JDBC driver is not bundled with Stash, you will need to download and install the driver yourself.
<Stash home directory>
/lib
directory (for Stash 2.1 or later).If Stash was configured to use Microsoft SQL Server by manually entering a JDBC URL, please refer to this guide.