404 Error Connecting to ZCC after MS SQL Database Move

[BMcDowell1]

Starting with ZENworks 11.4.3 running on Windows Server 2008 R2 with MS SQL 2008 R2 for the external database server running on the same physical server. I know, not a recommended configuration, even for 15 devices in a lab. The project is to move the database server to a new MS Server 2012 platform running MS SQL 2014 SP2.

I backed up the ZENworks and Audit database on the old SQL server and restored them on the new SQL server - no issues. My understanding is that should preserver the DBO accounts on the restore and looking at the new databases that appears to be the case. I updated the ZDM.XML and ZENAUDIT.XML files with new server name, database file path. SQL port is 1433 on both sides; I did add 1433 to the firewall exceptions on the new SQL server. No named instances. I think that's everything to know about the configuration.

When I restarted services on the primary server and launched the ZCC URL, I get a "404 Error - The page requested could not be found. Refer ZCC.log for error messages or additional information." Amusingly, a search of the device (Windows 10) on which I]m trying to access the ZCC does not show a ZCC.log file in a search. I tried the ZCC from the primary server itself and get the same error; on that device there is a ZCC.log file in the expected location but it hasn't been updated in over a day.

I have verified that I can ping all the servers from each other, and just to make sure I've updated the hosts file on each server to identify the server/IP addresses. There is no domain; everything is running in workgroup. I did try disabling the firewalls on each of the servers - as well as both at the same time - but that didn't help the situation.

This was supposed to be easy, but clearly I've missed something.

[CRAIGDWILSON]

Can you logon to the Moved Database using DBISQL with the proper creds?
From the ZCM Side, normally it is nothing more than updating the IP Address/DNS Name of the database server.

[strj500]

My guess is a user account credentials for the DB is perhaps not correct/matching with the new moved DB instance, which cautions me to provide this:



If user password of the database is changed, then you must change the password in the dmaccounts.proprities and zenaudit_ dmaccounts.proprities files. (located in ZENworks_installation_path\conf\datamodel on Windows and in /etc/opt/novell/zenworks/datamodel on Linux). It is recommended that do not use the SA user for ZENworks database access.

username=password

The password will be encrypted automatically when you restart the ZENworks services.

-----

If it's not that, make sure the dns name/ip is correct in ZDM.XML and ZENAUDIT.XML for the new DB. Sometimes the logs can be handy in identifying why the connection isn't working.

Also if you the ZDC DB verify tool with the -l option it may log more details than other logs (password problem, connection problem):

https://www.novell.com/documentation/zenworks114/zen11_utils/data/boq441g.html

[BMcDowell1]

So the advice in the two earlier comments is all quite useful - but this turned out to be rather more devious: when the MS SQL database was restored on the new server, the database owner (DBO) accounts were not properly created.

In the Management Studio, you can see two places where users are listed: under Databases > [database] > Security > Users and under Databases > Security > Logins. The restore event correctly created the user under the database itself, but not the in the other location. The remedy is to a) carefully transcribe all the configuration settings for the accounts (one for the ZENworks database, one for audit), preferably from the original source database server, b) delete the users from the databases themselves, c) add new users under the Security > Logins folder using the transcribed configuration settings, and finally d) go into each of the users and under Databases > [database] > Security > Users tick the check box for "db_owner" under the Membership section of the account properties.

You can read more about the core problem here - http://dba.stackexchange.com/questions/40844/after-moving-database-backup-restore-i-have-to-re-add-user. There is also a Microsoft knowledgebase article on the topic - https://support.microsoft.com/en-us/kb/918992. Both imply that you have to go through some effort to realign account SIDs; so far for me, simply deleting and manually recreating the accounts has worked.

[AndersG]

BMcDowell,
> The restore event correctly created the user under
> the database itself, but not the in the other location.

That is a known PITA with MSSQL databases in general. Thanks for
reminding me 🙂

--
Anders Gustafsson (NKP)
The Aaland Islands (N60 E20)

Have an idea for a product enhancement? Please visit:
https://www.novell.com/products/enhancement-request.html

[BMcDowell1]

Glad I could be of service!