02-22-2012 03:33 PM
I'm upgrading SLX 7.5.4 remote clients from SQL 2005 Express to SQL 2008 R2 Express. Upgrade of SQL runs fine. After upgrade, I can open SLX without any issues. But when I try to attach a new database extract, the attachremote program is able to detach the exiting database, and extract the sxd file into c:\settings and users\all users\Saleslogix\databases as an mdf. However, at that point I get a permissions error (unable to open file). If I manually copy that mdf file to another location, I can then use the attachremote utlity to attach it, and it attaches fine. However, when I try to log into SLX, I get a "Database login failed.: Login failed for user sysdba".
We are on XP SP3.
Seems like it is probably something simple...
Solved! Go to Solution.
02-22-2012 03:38 PM
Installed the 2005 backwards compatibility stuff. Using the SQL Server Native Client 10.0 rather than the 'SQL Native Client'.
02-23-2012 08:16 AM
If it was Win 7 I'd suspect UAC, but on XP it can't be.
What version of SQL do you have on the Host Database where you cut the Remote Databases?
02-23-2012 09:18 AM
As far as which SQL Client to use is concerned, we do the following:
A - SQL2005 -> SQL Native Client
B - SQL2008 -> SQL Server Native Client 10.0
C - SQL2008R2 -> SQL Server Native Client 10.1
02-23-2012 01:54 PM
Thanks for responding Emma and RJ. Good to have the client version recommendations.
Turns out what I was missing was the sysdba password. Somehow that got out of sync, but our dba went in doors unknown to me to figure out exactly what the issue was. All seems good now.
02-24-2012 09:53 AM
Well, I have to update once again. Not to disagree with our DBA's analysis, but this is still an issue. However, I have found a resolution. Hopefully, it will save someone else some grief in the future.
This *seems* to only affect upgrades from SQL Express 2005 to SQL Express 2008 R2, but that is only because that is all I have tested.
This link below from one of the Sage KB sites provided the key. (Not the regular SLX customer portal site... not sure exactly what site this is since the Customer Portal KB is down for an upgrade right now, but this is still available....).
Once you have SQL Express 2008 R2 installed, the SLX attachremote routine will move the location of the SLX database from the c:\Program Files\Microsoft SQL Server\MSSQL.x\MSSQL\Data directory to the C:\Documents and Settings\All Users\Application Data\SalesLogix\Databases directory. (Probably SQL 2008 R2 really doing it in anticipation of everyone moving to Windows 7 and the new limited (or “increased” depending on where you are standing) user security rights in Windows 7)
That directory needs to grant the user group "Everyone" the "Full Control" security permission or else the attachremote will not be able to create the required mdf and LDF files. It appears that the mdf can be created, but it seems that the LDF file can't be created until the permission is granted. The problem is that that directory won’t yet exist. The attachremote routine will create it, but not with sufficient permissions.
So, here were my steps:
Emma, think maybe we can get someone to document this in the SLX KB?
03-12-2012 11:02 AM - edited 03-12-2012 11:05 AM
We found this was only an issue running an upgrade from SQL Express 2005 to SQL Express 2008 R2. We continued to have varying results on diffferent workstations following the same procedures. Turned out to be much less messy to upgrade 7.2.2 to 7.5.4, then UNINSTALL all SQL Express 2005 components, reboot, and do a new install of SQL Express 2008 R2. Re-install the 2005 native client (probably could have just left the existing one rather than uninstall that piece of 2005), install the backwards compatibility component, then cut and attach a new remote database extract.
Virtually all issues went away. Didn't have to do any of the above. For anyone going from 2005 to 2008 express, I'd recommend avoiding the Upgrade, and do a new 2008 install. The only issue we had is that we had to drop to a CMD prompt and use sqlcmd commands to change the sysdba password. (Login failed for sysdba when opening the new database) for about 25% of the users. I think that is unique to us though as the sysdba password between 2005 and 2008 was changed, and for some reason the local instance needed to be reset to what the server and extract had.
03-12-2012 01:10 PM