Created By  inflectra.mike Thursday, August 16, 2018

The steps to perform when needing to move the database to another SQl Server, or when changing the DNS name of your SQL Server.

To configure the application to point to another SQL Server, or a moved database, follow these steps:

  1. Restore or transfer the database to the new server. (If you are simply changing the DNS name of the server, skip to # below.)
  2. Create a new SQL Login using SQL Authentication. Turn off password policies, and make sure the login is not required to change password on next login. Remember the password and SQL Login for use later.
  3. Create a mapping of the SQL Login to a database user. (Existing or new.) Give the SQL Login 'public' and 'db_owner' permissions on the SpiraTest or SpiraTeam database.
  4. Once the database, database user, and SQL Long are created, you can point the application to the new SQL setup.
  5.  
  6. Use Windows Explorer to open up to the SpiraTeam installation directory (usually C:\Program Files (x86)\SpiraTeam\)
  7. Find the file 'web.config' and open it up in a text editor.
  8. Find the section, about a page down, that looks like:   
        <connectionStrings>
            <add name="SpiraTestEntities" connectionString="[...]" providerName="System.Data.EntityClient" />
        </connectionStrings>
        The [...] will be a string of identifiers, separated by semicolons.
  9. Look for the token that reads "Password=....;". Replace the string there with the new account's password used to log into SQL. If you don't see that, then you probably have it set to Windows Authentication, and you'll need to switch over the connection to SQL Server Authentication or keep Windows Authentication and change the Domain user that the SpiraTeam IIS Application Pool is using to have permissions to access this database.
  10. Look for the token that reads "User ID" or "User Login", and change that to your new user's login.
  11. Look for the 'Initial Database' or 'Initial Catalog' token, and change that to the new DB's name.
  12. Look for the "Data Source=...". Replace the string there with the server and instance. (For example, if the server name is SQL1SVR, then it would be SQL1SVR\ for the default instance, or SQL1SVR\SQLEXPRESS for the SQLExpress instance.
  13. Save the file.
  14. Try to access the application. If it's successful, you will get a login page. If not, check the Web Server Windows Application Log, and it will likely give an error about the SQL User not logging in properly.
  15. Note: If you are on v3.2 or later, the 'ConnectionString' above will be HTTP encoded. When replacing values, be sure to not ruin any of the encoding tokens, like &amp; and &quot;.

Provider Connection Strings

If you are using Windows authentication to connect to the database you will see:

provider connection string=&quot;Trusted_Connection=True;Initial Catalog=[database name];Data Source=[server\instance];&quot;

If you are using SQL Server authentication to connect to the database, you will see:

provider connection string=&quot;Password=[password]; User ID=[login];Initial Catalog=[database name];Data Source=[server\instance];&quot;

 

Article Info
  • Last Updated: 8/16/2018
  • Article ID: KB30
  • Views: 3876