asked
Problem migrating MSDE2000 to SQL SERVER 2008
I have MS Small Business Financials which is no longer supported. SBF is running on the standalone desktop edition of sql server that was available on the SBF install media. Problem: Need to migrate to real db for management and data expansion reasons. MS mostly covered this in [KB 878449][1] and I'm using that for baseline docs. KB Article refers to scripts to migrate users, jobs and other items which I can not locate. Installed SQL Server 2008 Evaluation server on xp laptop running SBF with SQL Desktop edition. Installed in Mixed mode with Windows and database users. Install does not allow simple admin passwords. Forced to provide complex password. SBF data has simple password for SA user. SQL Server install does not allow simple passwords for SA user. The SQL backup I took from SBF running the desktop database is available for import into SQL server in a process that seems to go smoothly. I'm stuck with a couple of minor points... 1) it appears as though the SA user does not have access rights to the tabled created after the server was installed. Need to create DYNAMICS and SMB01 databases in server and SA user needs r/w privs and this user does not appear to have those rights. 2) the SA user password is now inconsistent between the database (2008) and the SA user in the applications data file. 3) supplemental scripts referred to in the KB article like the PJournal jobs are not available. can someone help me finish this project please. [1]:
http://support.microsoft.com/kb/878449/no
Why are you using evaluation version? The replacement for the MSDE would be using SQL Server 2008 R2 Express Edition. It would be the most current version of SQL Server to use. The issues you have regarding setting a simple password is the default security features of SQL Server 2008. MSDE did not have this ability and therefore let you pretty much set it to whatever you wanted. Although not advised you can disable this feature on SQL logins only within SQL Server. Simply uncheck or disable [CHECK_POLICY][1] for the account. You will also find this option under the properties window of the login in the GUI (SSMS). The "sa" account you reference, if this is the actual "sa" account created when SQL server is installed should have sysadmin rights in the SQL Server instance. It should be able to get to anything it wants/needs. If you selected mixed mode during the installation it would have prompted you to set the password. The fact that it requires a complex password it is to be expected that it no longer matches the password from your MSDE instance. It has been a lot of years since I have worked with Microsoft Dynamics software, but it would be advised to create a "admin account" for your application, in place of it using the "sa" account. This would allow you to apply least-privilege principal to your application accounts. Regarding (2) and (3), since the password has changed you would need to consult Microsoft Dynamics documentation on changing the password within the data file. The PJournal jobs scripts, the KB does reference a link to download or access them. It requires you to create or use your current Microsoft Dynamics account (Live ID). [1]:
http://msdn.microsoft.com/en-us/library/ms189828.aspx
I am using 2008 evaluation version because its free. Im testing this migration procedure for my employer, and eventually some of their customers. When we get ready to implement for real we will get a current version. There is a current question as to if we can make it work at all with 2008. I solved my own password inconsistency problem by changing the SA user password after install and checking the ignore policy box. (Interestingly enough, this is buggy as there is no password policy on this box.) I am unable to download the scripts mentioned in the kb article because I am not a dynamics customer. I can log in with my windows live credentials but I can no associate my winlive account with a dynamics account as I dont have a code for that.
