Downgrading MSSQL2005 to SQL Express

luca · Post by **luca** » Fri Jun 09, 2006 3:10 pm

Hi!
I'm in the process of rebuilding my box, and since previously I installed MSSQL05 just for Vault and for a little bit of experimenting, I would like now to switch to SQL Express because of license issues. Is it possible to migrate my Vault database to SQL Express? Which steps should I take (roughly)? Suggestions?

Thank you in advance
--
Luca Leonardo Scorcia
The Price of Freedom is Eternal Vigilance.

lbauer · Post by **lbauer** » Fri Jun 09, 2006 5:51 pm

You can move to SQL Server 2005 Express only if your sgvault database is less than 4 GB, which is the database size limit for SQL Server Express.

Steps would be (roughly):

-- Backup your sgvault database from SQL Server 2005.
-- Uninstall Vault.
-- Install SQL Server 2005 Express
-- Restore the backup to SQL Server 2005 Express.
-- Reinstall Vault.

Note: when installing Vault Server with SQL Server 2005 Express, use .\sqlexpress (local machine) or machinename\sqlexpress, as SQL Express is installed as an instance.

luca · Post by **luca** » Mon Jun 12, 2006 1:37 am

I followed your instructions and have been able to restore my database and even reinstall correctly vault, however when I try to connect using the Vault Client it says:

The Vault Server could not establish a connection to the database. Please check the server's log for detailed information. (FailDBConn)

The server log is a little bit more helpful:

----12/06/2006 9.35.34 luca--localhost(127.0.0.1)--SSL Disabled Vault Failed to establish a connection to the database.
----12/06/2006 9.35.34 luca--localhost(127.0.0.1)--SSL Disabled System.Data.SqlClient.SqlException: Cannot open database "sgvault" requested by the login. The login failed.
Login failed for user 'PENTIUM4\ASPNET'.
in System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject)
in System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)
in System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
in System.Data.SqlClient.SqlConnection.Open()
in VaultServiceSQL.VaultSqlConn.OpenConn()
in VaultServiceAPILib.VaultServiceAPISystem.GetOpenSqlConn(HttpApplicationState has, VaultLoginInfo vli, VaultSqlConn& conn) in System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject)
in System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)
in System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
in System.Data.SqlClient.SqlConnection.Open()
in VaultServiceSQL.VaultSqlConn.OpenConn()
in VaultServiceAPILib.VaultServiceAPISystem.GetOpenSqlConn(HttpApplicationState has, VaultLoginInfo vli, VaultSqlConn& conn)

However, if I open Management Studio Express, I can see that PENTIUM4\ASPNET is owner for the database. Is there a way to reset the permissions? What can I do?

lbauer · Post by **lbauer** » Mon Jun 12, 2006 8:08 am

If Vault and and SQL Server are on the same machine, try deleting and recreating the login and database user for 'PENTIUM4\ASPNET'.

If your SQL Server is on a different machine, the 'PENTIUM4\ASPNET' won't work, since it's local to PENTIUM4. In that case, re-install Vault using a custom account or SQL Server authentication. If you use SQL Server authentication, the installer creates an sgvaultuser account in SQL Server.