New install upgrade from 6.5.3. to 6.5.4 sp2

i currently have a windows 2000 server running sql 2000 with Visual enterprise 6.5.3. i have a new server running windows 2008 and sql 2008. i have installed the runtimes and install Visual application server. i have created a new database on sql 2008 and restored the database from a bak file from the sql 2000.

i am unable to log into visual using the sysadm account and i have setup the same password as before when creating it in sql 2008.

have i missed a step? this is the first time i have tried to perform an upgrade like this so any and all help is greatly appreciated.

Find more posts tagged with

VISUAL - Enterprise General Discussions

Comments

Did you create the SYSADM account in 2008(under Security), not the Database user? Also I have been told to disable Enforce Password Policy. Also I have been told the DB needs to be set to SQL Server 2000 (80). The following is what I have come up with based on conversations with others in converting from a Windows Server 2000/SQL Server 2000 to Server 2008 and SQL Server 2008:

Conversion From 6.4.1 to 6.5.4 and SQL Server 2000 to 2008
1)Run Security setting list form active database
2)Make sure no users are on system
3)Turn off machines and verify no users in Enterprise manager
4)Apply Latest SQL updates to original database
5)Run Check Orphans Script
6)Verify Users that are Orphaned - Find Orphans.sql
7)Run Script to allow users to be deleted from Tables/sysusers
a.Allow Updates Ad Hoc.sql
8)Delete orphaned users from sysusers
9)Delete users from Database via Visual Admin under Sysadm ( I was told this was needed when going from 2000 to 2005/2008)
10)Run Update Statistics
11)Backup Database
12)Detach database so it cannot be used
13)Copy database to new server
14)Restore to New server
15)Verify Database is set to SQLServer 2000 Compatibility (80)
16)Make sure SYSADM is set up as a user
17)On Separate Computer Log in to New Database
18)Allow conversion to 6.5.4 to run, it may take awhile
19)Run SQL Updates from spare computer
20)Open Database and under HelpAbout verify SQL level

Hope this helps.

Hi,

This is what I do when creting new instances of SQL server. It will work to transfer all username and password between servers of any SQL Server version.

Before restoring your database on the new server

On your current production server.
1) Create the sp_help_revlogin proceedure in Method 1

support.microsoft.com/.../246133

Method 1 will work for Visual because the ERP system handles all security.

2) Run the proceedure on your current production system.
3) Copy the output to query anaylyzer on your new server.
4) Run the script on the new server - All usernames and password will now be restored to your new server.
5) Restore your database to the new server.
6) Open the restored database tree in Microsoft Management Studio
7) Select the tables folder
8) In Object explorer detail, Sort view by schema
9) Go down to the bottom and select all tables owned by the SYSADM Schema
10) Right click and select delete(DROP) and drop the tables. They are all temp tables the Visual did not clean up after use.
11) Go to Security, in the restored database, highlite users folder.
12) Select the SYSADM user and right click to Delete(DROP) including Schema objects, in object explorer detail
13) Go to Security, Instance security, and highlite logins
14) Right click the SYSADM user and select properties.
15) Go to "User Mappings"
16) In the top half, select the new visual database.
17) In the bottom half, Select db_owner
18) Select OK
19) Log into your new instance of Visual, on the new server, as SYSADM or any current Visual user.( Make sure your SQL.ini file is pointing to the new server as well as the old)

Sean, i followed your post with one exception is that i had the database already restored before i started. i am able to login now but i get a message that the dtabase is currently version 653 and it needs to be upgraded to 654. when i click yes to upgrade i get the conversion untility. i type in the correct information but it fails with the error "Release 6.5.4 requires SQLserver 2005 and above. Upgrade failed" even though i am using SQL 2008

i have also found that i can only login and get the upgrade message when i have the old server referenced first in the SQL.ini file. if i put the new server then it has a SQL error and fails.

[Updated on 4/21/2010 8:37 AM]

birstSpaceMonitoring.html.zip

0906221039165974.pdf

I have not had that problem doing the upgrade as I too am using 2008. However in running the script (item 2) I get a number of the following error:

Msg 15433, Level 16, State 1, Line 1
Supplied parameter sid is in use.

This is the first part of the resulting script from item 1:

/* sp_help_revlogin script
** Generated Apr 21 2010 6:24AM on DBSERVER */

DECLARE @pwd sysname

-- Login: JGORDON
SET @pwd = CONVERT (varbinary(256), 0x0100CD4BFC5D92E64AAC132CAB6A228CAB93FB6D47D4A5A4A4DF13B909C607854C819FE19BA5EB377A2B0E64732A)
EXEC master..sp_addlogin 'JGORDON', @pwd, @sid = 0x00DE1458CC768445A1325C871884567B, @encryptopt = 'skip_encryption'

Thanks

Hi,

If the SID exists already, the script cannot create the user. That's why it's better to run the script before any Visual Database is restored to the new server.

You will also get error's when logins that exist already are recreated. ie domain accounts.

I haven't seen the error release 654 requires 2005 and above before. I have only tested the update on 2005 without a problem.

Do you have the latest version of the conversion utility?

I'm just building a SQL 2008 server to test.

Sean

That is what I had thought. I had been told by Lilly that users did not transfer over so I had manually deleted them and moved a database over that I am testing. So I was trying this on another copy but it is probably seeing the current database I had already moved.

As far as the conversion I went from 6.4.1 on SQL 2000 on a 2000 server to 6.5.4 on SQL 2008 on a 2008 server and did not get an error message.

i have the latest version of the conversion utility. i have notice that i am getting a SQL halt error even if i view just the About information.

i am wondering if i should delete the database and create a new one and start over again.

Hi,

The actual database users transfer over, it's the sql server instance logins that don't.

That is what the script is recreating.

I would start fresh

Uninstall SQL server from your test server and reinstall.

If you look in the logins after there should only be a couple.

Then run thru the proceedure described early restoring you database in the sequence explained.

You will still get a couple of errors during the login recreation for the domain accounts but that's normal.

i am still getting the same "SQL Error: Invaild Cursor. Halt Application?" if i choose yes i get the error "database upgrade was not successful" and if i hit no twice i get "release 6.5.4 requires Sqlserver 2005 and above. Upgrade Failed."

in the SQL.ini should the new server be listed before the old or the old before the new? also when the conversion untility open the SA password is already populated. for the SA password should i use the old or the new one?

After deleting the Database I was working on from SQL 2008 I then went through Sean's instructions and it worked great. The only thing that happens is that I get a notice that the password configuration rules have changed and it needs to be reset. But then all that you need to do is re-enter the password as the old on and then the new.

As far as the SA password, Brandon, it is the one used on the new server. Also as I am running the new visual on a test machine I do not have the old server in the SQL.ini file. I would also say that once you get the upgrade going let it run as it took longer in SP2 than it did in SP1 of 6.5.4 and at times it seemed to stall. But just letting it go seemed to work.

VID032_SAAEA_Creating a New Setup, Inquiry or Transaction Screen.zip

Sean: Well I may have spoken too soon. Any idea why some users would work just fine and others generate the 19005 error that there is a problem? I can delete the user and recreate them. I did try and see if I needed to change the password but it appears that 6.5.4 does not even allow the SYSADM user to change the password, unless there is another way to do it.

i have no issue logging into Visual. my problem now is that i am getting errors trying to use the conversion utility to convert the 653 database to 654 after the system accepts my login.

Hi,

Is there a password policy set for the users that generate the error. SQL 2005 and 2008 use the local server policy to create password rules.
See this.
www.aspfaq.com/.../show.asp

If the user in question has a password of "Password" and you type in "password", without the capital "P", you will get the error.

If some user work and some don't, I would suspect that they have capitals and lowercase in there password. Try resetting the user password to all lowercase.

The SYSADM user is the only user without a "#" alter ego. You can change this password in SQL server, under logins, without affecting Visual. This is the only one you can change that way.

When you start up the conversion utility, you enter the "SA" password of the new server and the "SYSADM" password of the new server.
Di9d you try logging in to the restored DB with your current version of Visual and make sure that it's all working?

When the error comes up, hit the details button and see what it was trying to run.

HI,

Another thought, what version of SQL server is your production system running on?

I would modify your SQL.ini file to only have the TEST server in it while you are doing the upgrade. There have been a few accidental upgrades of production servers and you might be getting that error because you are logging in to the SQL server 2000 server.

Hi Sean:

Where are the user passowrds reset? I tired to change passwords as in 6.4.1 by loging in as SYSADM but 6.5.4 does not seem to work as it says the user (SYSADM) could not change passwords.

By the way, thanks Sean for all the help.

now after i remove the reference to my old server from the sql.ini i get the 19005 error on the server and on a client that i have installed. it seems that i can only log into the old server when it is in the sql.ini. this would explain the other error message that it needed SQL2005 or higher since the old server is SQL2000. it was trying to convert the old server.

Hi,

The user passwords have to be changed thru Visual the same way you do it in earlier versions.

Can you check the SYSADM user settings in SQL Server.
Right click the SYSADM user, under logins, and select properties.
Select user mappings. Is db_owner checked off for your test Visual restore?

Change your SQL.ini to point to the new server.

There are 2 or 3 sections that you need to change.

[SQLSERVER]
;
dbname=testservername,dbname

[win32client.ws32]
serverpath=testservername,ipaddress/*

and if you have this section.

[winclient.wsock]
serverpath=testservername,ipaddress/*

Try changing these and log in to the test server with your current version of Visual. If you can log in and get the Visual menu, go into SSMS and open management, select activity window and see if your computer is logged in as SYSADM to your test server.

I went into SYSADM and db_owner was checked (default Schema is dbo). But in Visual I when I go to change user passwords I still get an error that says "This user does not have permissions to change another users password"

When you select the update the password, does it ask for the sa password? You need to put the sa password in for the test server.

No it did not ask for the sa password.

I just found the issue with the passwords not working. It seems, and I am not sure why this was not for everyone, that on the ones that were not working Visual had the password all capitalized. So once I type in the password I go through the configuration change thing and can reset it correctly.

As far as SYSADM not being able to change passwords I have an incident request out as on looking at INFOR's knowledge base I noticed it had a note that they know there is a problem and they are looking into it

I just talked to INFOR and the issue was that since SQL2005, we were on 2000, the SYSADM user needed to have "sysadmin" cheked under Server Roles on the user Login under the SQL Server users. Now it works fine.

i really thank you both tkonvalin and Sean.

i have tried everything but still no go with the new 654 setup. i have tried the changes to the SQL.ini file, i have tried upper case and lower case, everything you both have suggested. nothing.

Hi,

I'll set up SQL 2008 and Upgrade my 6.5.2 to 6.5.4 on it tommorrow and see if I run into problems.

any luck with the SQL 2008?

Sean, were you able to setup the 2008 ???

Hi,
I haven't had a chance to set up SQL 2008 yet.
I will try to get this set-up today for testing.

Sean any luck on the install with SQL 2008?

Quick Links

Popular Tags