Print Page | Close Window

Upgraded from 8 to 9 in test env, have ?

Printed From: Microsoft Dynamics Forums
Category: Microsoft Dynamics GP (Great Plains)
Forum Name: GP - Installation and Administration
Forum Discription: Installation, Upgrade, Service Pack, Security, Database, Administration, SQL Server, Login Issues, System Errors, Performance, EDI, Business Portal, and related topics
URL: http://www.MicrosoftDynamicsForums.com/forums/forum_posts.asp?TID=869
Printed Date: July 03 2020 at 8:03pm


Topic: Upgraded from 8 to 9 in test env, have ?
Posted By: i64X
Subject: Upgraded from 8 to 9 in test env, have ?
Date Posted: December 04 2006 at 2:57pm
I set up a VMWare server with Server 2003 on it. Our old database was a GP 8 database running on SQL 2000. I exported the database to the Server 2003 VM and installed SQL 2005. I installed GP 9 and it seems as though I've got everything working fine. I followed the guide and have upgraded everything. I can see all of our old data, have our reports.dic and forms.dic converted over and everything, and I can see all of my users in Advanced Security.

Until now I've been logging in as SA with no problem. Today I tried logging in as a normal user and it won't let me. I get an error message that says "This login failed. Attempt to log in again or contact your system administrator." The password for the account I'm trying to use is correct. I've also tried other accounts and unless I'm logging in as sa, I get the same error.

I have read that SQL 2005 requires case sensitive logins. I've made sure I'm using the correct case and that hasn't helped either.

When I go in to advanced security I can see all of my users and all of their properties. When I log in as SA and go to Tools > Setup > System > User Access I can see my list of users. When I click on any user other than sa I get the error message "The user could not be added to one or more databases."

When I go in to Tools > Setup > System > User I can again see all of my users. When I click on any user other than sa I get the error message "The user does not have a corresponding SQL login. TO create a SQL login, enter a password and choose save." When I try to do that, I get this error: "The creation of the login failed for an unknown reason. Contact your SQL server administrator for assistance."

I'm supposed to do this upgrade in a couple weeks and I've tried everything to figure this one out but I can't. I've already scoured Google and Dynamics Customer Source and have found nothing. If I go in to the SQL Server 2005 app I can see that my list of users do have accounts in the Security folder under my DYNAMICS and CONC (my company) databases. I still think it has something to do with the new security in SQL 2005.

Any ideas? :(



Replies:
Posted By: JohndeKock
Date Posted: December 05 2006 at 2:41am
Check your SQAL server properties page. Make sure that you are using "SQL Server and Windows Authentication mode" for Server authentication (on the server properties page). I suspect that AD integrated security is lurking around there somewhere.

-------------
Have fun!
John


Posted By: i64X
Date Posted: December 05 2006 at 6:34am
That's the mode it's set to.

IS anyone else running GP9 on SQL 2005? Can you log in to GP on the server as a user other than SA?


Posted By: i64X
Date Posted: December 05 2006 at 7:08am
Found another way to do it. I had a test GP login with the user name of Jason (my name) with Jason as the password. I created the user under SQL Server Management Studio under Server -> Security -> Logins. When I right click on that user and go to Properties, and then select SQL Server Authentication I set the password to Jason to match the GP login.

I leave the default DB to master (tried changing it to Dynamics and I got the same error upon login as before). If I hit ok here and try to log in to GP I get a new error that says I don't have access to the Dynamics database.

If I go back in to the properties for that user I found out some things. If I go under Server Roles and make the user a sysadmin I can log in to GP and do everything just fine. I don't want to do this for all of my users though, obviously.

Under User Mapping I found what looks like a place where I can assign the user rights to databases. I tried to check my company database for GP as well as the Dynamics database, but when I click OK to accept the changes I get an error that says the user Jason already exists in those databases.

When I go in to those databases in SQL Mgt Studio I can find the user Jason under Security -> Users (along with all of my other users) but even when logged in as "sa" I still can't delete that account in order to assign permissions from the higher level.

I'm 100% confident that the root problem lies in SQL 2005 permissions now and not in GP... my problem is that I have experience with SQL 2000 only, and SQL 2005 has changed so much. :(

Anyone know how I fix this?


Posted By: JohndeKock
Date Posted: December 05 2006 at 7:41am
Do not create your GP users in SQL! Let GP handle the process for you. (You will find a Group called DYNSA which handles much of the permissions).

Firstly, because your database was exposted, you will want to check that it is all Okay.

Run the following in SQL:
DBCC CHECKDB
DBCC CHECKALLOC
DBCC CHECKCATALOG
Then you want to make sure that DYNSA has the correct permissions, so run the following (found in your client install folder under SQL):
DYNCHK.SQL
GRANT.SQL
(There is probably ome or two that I'm missing)!!

Then make sure that there is no remnant login in SQL. Then create the user through GP.


-------------
Have fun!
John


Posted By: i64X
Date Posted: December 05 2006 at 7:53am
All of those completed without error until I got to GRANT.SQL, which returned the following error...

Msg 15151, Level 16, State 1, Line 1
Cannot find the user 'DYNGRP', because it does not exist or you do not have permission.

It repeats that 1000 times and then at the bottom of the status window it says

"The query has exceeded the maximum number of error messages. Only the first 1000 messages will be displayed."

I have verified that DYNSA does exist under [SERVER] -> Security -> Logins -> DYNSA. There is no DYNGRP here.

Under [SERVER] -> Databases -> [Company Database] -> Security -> Roles -> Database Roles there's a DYNGRP, and there's also one under [SERVER] -> Databases -> dynamics -> Security -> Roles -> Database Roles, but there's not one under [SERVER] -> Security -> Logins or Server Roles.

Should there be?


Posted By: JohndeKock
Date Posted: December 05 2006 at 8:20am
No it does not need to be there. But I think that you have come up with the fundamental problem - You need to sort out DYNGRP (sorry I think I called it DYNSA earlier).

If I remember correctly there is a utility somewhere that does fix it.

-------------
Have fun!
John


Posted By: i64X
Date Posted: December 06 2006 at 6:48am
I tried to create a new user in Great Plains called "test" and this is what I found...

test appears under both databases where all of the other users appear. test is also made a member of the DYNGRP groups for both databases. When I try to log in as test, it tells me that I need to change the password to log in. I can't change it to anything without the program telling me "the password failed for an unknown reason." I tried everything - tons of chars, upper/lower/number/special, etc. nothing works.

That's broken, but at least it's acknowledging my login. I went back in to SQL Management studio to compare that user to the other users, which were there pre-upgrade. I found something interesting. The "test" user doesn't appear in the company database, he only appears in the dynamics database. Is this normal?

Some other things I found - none of my users (in either database) have anything filled in for "login id" and I can't change that because the boxes are greyed out. Not a big deal - I can re-create them all if need be. When I go in to GP and try to manage a user I can't save changes because it says that saving the user changes "failed for an unknown reason."

Creating user IDs wouldn't be a big deal so long as I could delete all of the current ones somehow.

Any idea why test's password change is failing? If I can figure that out and log in as a newly create user, re-creating my existing 12 or so users isn't a problem.


Posted By: i64X
Date Posted: December 06 2006 at 9:01am
Alright I figured out a workaround... I'll just type it here in case anyone searches for this in the future, since GP support on the Internet = sucky. :)

Basically the upgrade botched my users somehow. Nobody can log in and I get errors when trying to do anything with them. sa can log in and that's it. I also can't delete users, which is a problem.

I found a way to manually delete users, re-create them, and keep their security in tact. This might not be a good solution for BIG companies, but we have like 12-14 users so it's no big deal and will only take about an hour or so to fix.

Basically what you do is this - create a temporary user called tmp_accounting, for example, and then used Advanced Security to copy over an accounting user's permissions to that tmp_ account. We have Advanced Security and it's SUPER granular so I don't want to manually have to make note of everyone's settings.

After that, the security settings for that user are safe. I did this for every group we had - accounting, admins, sales, sales support, production, etc.

Since I wanted users to have the same names as before, I need to re-create the accounts, but first I need to delete them. If I try to do it in GP it gives me database errors. Basically what I did to get around this is go in to SQL Mgt Studio and go under the CONC (my company's database) and DYNAMICS database and go under Security -> Schemas and deleted the schemas that corresponded to all of my users names ONLY. Then I was able to go in to the Users container and delete the users accounts ONLY.

If you're doing this - DO NOT TOUCH any account you didn't create. Don't touch SA, don't touch ANYTHING but employees names or you'll screw something up.

After that, I was able to go in to Great Plains and manually go in to Tools > Setup > System > User and delete the users' accounts one by one.

I then re-created all of the users' accounts, and then went to Tools > Setup > System > User Access and gave them all access to the company again.

Then I went in to Advanced Security and, using the templates I created for each user group, copied the settings from the templates to the corresponding users to which they belonged.

At this point you could either keep all of the tmp_ accounts for further use (new hire, etc) or delete them. I set up a secure password on all of mine, so I'll probably just keep them for ease of use later.

My users are now all able to log in, have the same permissions as before, and everything seems ok.


Posted By: Rohtash Kapoor
Date Posted: December 07 2006 at 8:06am

Thanks for sharing the workaround. Thumbs%20Up



Posted By: i64X
Date Posted: December 07 2006 at 8:12am
No problem, I hope it helps someone out.


Posted By: Joseph A Nader
Date Posted: December 08 2006 at 2:26pm
So what really is the difference between SQL 2000 and SQL 2005 with respect to performance, security or any other issues...???
 
Would appreciate the feedback, cause Iam interested in upgrading soon...
 
Take Care


-------------
Joseph Abou Nader, PMP



MCT
,MCTS,MCBMSS,MCSA


Posted By: i64X
Date Posted: December 11 2006 at 6:03am
I'll let you know if we see any increased performance once I upgrade in a real environment. The upgrade I recently performed was just on a VM, so performance is bad no matter what version of ANYTHING you're using. :)

SQL 2005 does seem to be WAY more secure than SQL 2000. Maybe too secure. On a default install of SQL 2005, not even sa can log in until you run a utility to configure who can log in where. It's pretty crazy.


Posted By: Bupe
Date Posted: August 15 2007 at 10:36am
Hi! I would like to upgrade from GP 8 on SQL 2000 to GP 10; can you help? Any guidelines?
 
Bupe
 
educose@yahoo.com


Posted By: apopa
Date Posted: December 17 2007 at 2:23pm
Have you tried this.
 

SQL Server Logins and Users


Overview

Although the terms login and user are often used interchangeably, they are very different.

·         A login is used for user authentication

·         A database user account is used for database access and permissions validation.

Logins are associated to users by the security identifier (SID). A login is required for access to the SQL Server server. The process of verifying that a particular login is valid is called "authentication". This login must be associated to a SQL Server database user. You use the user account to control activities performed in the database. If no user account exists in a database for a specific login, the user that is using that login cannot access the database even though the user may be able to connect to SQL Server. The single exception to this situation is when the database contains the "guest" user account. A login that does not have an associated user account is mapped to the guest user. Conversely, if a database user exists but there is no login associated, the user is not able to log into SQL Server server.

When a database is restored to a different server it contains a set of users and permissions but there may not be any corresponding logins or the logins may not be associated with the same users. This condition is known as having "orphaned users."

Troubleshooting Orphaned Users

When you restore a database backup to another server, you may experience a problem with orphaned users. The following scenario illustrates the problem and shows how to resolve it.

Use master
sp_addlogin 'test', 'password', 'Northwind'

SELECT sid FROM dbo.sysxlogins WHERE name = 'test'
0xE5EFF2DB1688C246855B013148882E75

Grant access to the user you just created

Use Northwind
sp_grantdbaccess 'test'

SELECT sid FROM dbo.sysusers WHERE name = 'test'
0xE5EFF2DB1688C246855B013148882E75

As you can see, both SID's are identical.

Backup the database

Use master
BACKUP DATABASE Northwind
TO DISK = 'C:\Northwind.bak'

Copy the Backupfile to another Maschine and SQL Server and restore it as follows:

RESTORE FILELISTONLY
FROM DISK = 'C:\Users\Zahn\Work\Northwind.bak'

Northwind
Northwind_log

RESTORE DATABASE TestDB
FROM DISK = 'C:\Users\Zahn\Work\Northwind.bak'
WITH
 MOVE 'Northwind' TO 'D:\DataMSSQL\Data\northwnd.mdf',
 MOVE 'Northwind_log' TO 'D:\DataMSSQL\Data\northwnd.ldf'

The restored database contains a user named "test" without a corresponding login, which results in "test" being orphaned.

Check the SID's

Use master
SELECT
sid FROM dbo.sysxlogins WHERE name = 'test'
0x39EE98D37EAC2243B7833705EC1C60E3

Use TestDB
SELECT
sid FROM dbo.sysusers WHERE name = 'test'
0xE5EFF2DB1688C246855B013148882E75

Now, to detect orphaned users, run this code

Use TestDB
sp_change_users_login 'report'

test 0xE5EFF2DB1688C246855B013148882E75

The output lists all the logins, which have a mismatch between the entries in the sysusers system table, of the TestDB database, and the sysxlogins system table in the master database.

Resolve Orphaned Users

Use TestDB
sp_change_users_login 'update_one', 'test', 'test'

SELECT sid FROM dbo.sysusers WHERE name = 'test'
0x39EE98D37EAC2243B7833705EC1C60E3

use master
SELECT
sid FROM dbo.sysxlogins WHERE name = 'test'
0x39EE98D37EAC2243B7833705EC1C60E3

This relinks the server login "test" with the the TestDB database user "test". The sp_change_users_login stored procedure can also perform an update of all orphaned users with the "auto_fix" parameter but this is not recommended because SQL Server attempts to match logins and users by name. For most cases this works; however, if the wrong login is associated with a user, a user may have incorrect permissions.



Posted By: colins
Date Posted: November 19 2008 at 2:50am
Hi
 
I have some 250 users that I need to do this for...is there an easier way of doing this other than deleting each user under the schemaCry


-------------
Colin


Posted By: daoudm
Date Posted: November 19 2008 at 6:20am
Guys,
 
You may need to check this script:
 
http://mohdaoud.blogspot.com/2008/11/drop-and-recreate-database-logins.html - http://mohdaoud.blogspot.com/2008/11/drop-and-recreate-database-logins.html
 
Regards,


-------------
Mohammad R. Daoud [MVP]
+962 - 79 - 999 65 85
mohdaoud@gmail.com
http://mohdaoud.blogspot.com



Print Page | Close Window