I ran into the problem because my first round of testing was upgrading our test system which easily fit into the SQLExpress limit of 10 GB. Our production system is more than 10 GB so I had to graduate to using the new production SQL Server for testing (though not actually in production yet). As part of that process I there are certain roles that need to be on the server and I figured it'd be easiest if Nintex Workflow provisioned all of that rather than doing it manually. I restored the production workflow database to the new server and when attempting to connect (Nintex Workflow Management --> Database Setup) to it I got this message in the browser: "Unexpected SQL error occurred. Server name: servername Database name: database name. Correlation ID: [.....]".
Using ULS Viewer found on MSDN I was able to find out that it's basically a Named Pipes server can't be found (error 40)... but what's odd is it's the server and database of the current Nintex database (though without the instance ID), not the new location.
Resolution:
I pulled up the database table on the existing Nintex Workflow database and changed it to the current system\SQL Instance. I rebooted the server (iisreset probably would have worked just as well) and was able to point the system to the new database on the production server. Make sure the database table on the new server is correct before attempting to connect to it from Central Admin or your 2007 workflow environment will break.
