Connecting Stash to SQL Server

Prerequisites

Back up your current database

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.

Create the SQL Server database

Before you can use Stash with SQL Server, you must set up SQL Server 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.

Connect Stash to the SQL Server database

You can now connect Stash to the SQL Server database, either:

• when you run the Setup Wizard, at install time,
• when you wish to migrate to SQL Server, either from the embedded database or from another external database.

When running the Setup Wizard at install time

1. Select External at the 'Database' step.
2. Select SQL Server for Database Type.
3. Complete the form. See the table below for details.
4. Click Next, and follow the instructions in the Stash Setup Wizard.

When migrating to SQL Server

1. In the Stash administration area, click Database (under 'Settings').
2. Click Migrate database.
3. Select SQL Server for Database Type.
4. Complete the form. See the table below for details.
5. Click Start Migration.

Use Integrated Authentication or 'Windows Authentication Mode' (Optional)

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-3035 - Getting 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:

1. Based on the JVM being used to run Stash, rename either the 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.
2. In 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.
3. Edit the %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.
4. Ensure the Stash process or service is running as the correct user to access SQL Server.  (Note that this user is generally a Windows Domain User Account, but should not be a member of any administrators groups, that is local, domain, or enterprise.)

Install the JDBC driver

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.

1. Download the appropriate JDBC driver from the Microsoft download site.
2. Install the driver file to your <Stash home directory>/lib directory (for Stash 2.1 or later).
3. Stop, then restart, Stash. See Starting and stopping Stash.

If Stash was configured to use Microsoft SQL Server by manually entering a JDBC URL, please refer to this guide.