Summary

The Microsoft SQL Server Databases for a Jira Align On-premise deployment can be hosted in different ways, such as:

SQL Server installation on the same server as Jira Align
- Connections are made to the local server by IP or by the server name. Example: sameserverasjiraalign.mydomain.com
Dedicated Microsoft SQL Server
- Connections are made to the remove / dedicated SQL Server. Example: customerserver001.customerlocaldomain.com
AWS RDS for Microsoft SQL Server
- Example: Endpoint just.an.example.c8z9qk2h1sm2.us-east-1.rds.amazonaws.com
Azure SQL Database
- Example: Server name companysqlserver.database.windows.net

To verify that the SQL credentials and server details are correct and functional, this article shows how to test connection and authentication to the SQL Server in an easy way using SQLCMD, ODBC connection, or UDL file

Environment

Jira Align Self-Hosted

Solution

The following SQL Server attributes are needed to test the connection:

Server hostname and Server port
SQL user ID and password

Consider the following example of a connection string

<connectionStrings>
 <add name="Main" connectionString="Data Source=192.168.1.10,1433;Initial Catalog=Agilecraft;Integrated Security=false;User Id=sqluser1;Password=Atlassian@321;Encrypt=yes;TrustServerCertificate=True;MultipleActiveResultSets=True;" />
</connectionStrings>

Test using Powershell


1	From the Jira Align web server machine: Open a command prompt or PowerShell
2	Use SQLcmd to connect to a SQL Server, for this example: sqlcmd -S 192.168.1.10 -d agilecraft -U sqluser1 -P Atlassian@321 If the 1> is shown, the authentication was successful	Successful Connection Failed connection
3	After successfully connected, you can run a simple query to confirm permission	`use AgileCraft press enter go press enter select top 1 VersionName from dbo.tblVersion press enter go Press Enter`
4	Another option is to use the command: `Invoke-Sqlcmd -HostName "(local)" -Database master -Query "SELECT @@VERSION" -OutputSqlErrors $true` Note that the user running the command on Windows needs to have access to the master table on the db

Test with an ODBC Connection


1	From the Jira Align web server machine: Run odbcad32.exe (ODBC Datasource Administrator)
2	Click on Add Select SQL Server Native Client Click on Finish
3	Enter a name for the connection Enter the server address with the port. format is 1.1.1.1, 2222 where 1.1.1.1 is the server IP and 2222 the port number Click Next
4	Enter the Credentials Login ID: the SQL User Password Click Next
5	No changes needed on the next 2 screens click Next Click Finish
6	Click on Test Datasource and review the results