Sychronizing Attain - SQL server not working

ecarmody

Hello,

Running Navision Attain 3.6 and using MS SQL Server 8

We migrated our native Navision Attain to SQL Server several months ago and all has been well. When we add new users we add them via Windows Logins screen and use Windows Authentication.

The issue now that we were unaware of is that the local Database Logins in now out of synch and new users are not showing on features that show user lookups.

When I try the Sychronize menu option nothing happens. No prompts, no error, no popup screen. The Database login list remains unchanged.

I assume that the Synchronize feature should pass the SQL Login info down to Attain and update the Database Login list?

Why would running the Synchronize menu option just do nothing?

Appreciate help.

Regards,
Eric

bbrown

You have the process flow backward. Sychronization takes the NAV security setup and pushes it down to NAV creating/updating the application role.

ecarmody

bbrown wrote:

You have the process flow backward. Sychronization takes the NAV security setup and pushes it down to NAV creating/updating the application role.

Pardon ... "takes the NAV security setup and pushes it down to NAV" ... not clear on what your getting at.

So how do we transfer new SQL logins to Navision database logins?

Thanks,
Eric

bbrown

ecarmody wrote:

bbrown wrote:

You have the process flow backward. Sychronization takes the NAV security setup and pushes it down to NAV creating/updating the application role.

Pardon ... "takes the NAV security setup and pushes it down to NAV" ... not clear on what your getting at.

So how do we transfer new SQL logins to Navision database logins?

Thanks,
Eric

Sorry about that..

"...takes the NAV security setup and pushes it down to SQL"

You don't transfer the SQL logins to NAV.

1. Create the SQL logins (SSMS)

2. Add them as database users (SSMS). Make no other changes. They only need to be members of Public (default)

3. Go to NAV add the same users to Database Logins

4. Assign Roles

5. Sync

garak

Do you have restore the Database from old SQL Server to new SQL Server :?:

http://www.mibuso.com/forum/viewtopic.p ... highlight=

If you have database login on SQL and in NAV and they doesn't work (because the database is resored on new Server), the stored SID is not the some. So, delete the logins in Nav and SQL, after deleting add the database logins in NAV.

ecarmody

Ok, we normally we've been adding our users to the database server and assigning them to the Navision database.

We use windows authentication with our domain, which shows the user id with the domain prefixed. Example: DOMAIN\userid

Once the user has been created/assigned on the SQL server, I go to Navision, Security, Windows Logins and add them there. They show in the Navision Windows list same as on server, DOMAIN\userid. I only need to enter "userid" and it resolves to "DOMAIN\userid". I then use the ROLES button on the Windows Login screen to assign roles.

When I go to the Navision Database Login screen and then try to add "userid" I get Navision message "... navision and sql security systems have not be synchronized successfully ... SQL server login "userid" does not exist on the "sqlserver" server."

I tried to enter the name both as "domain\userid" and "userid". The userid I try are valid on the server, in public role and assigned to Navision database; they also show in the Navision Windows Logins list.

How do I get them into the Database Logins?

Should I be adding them to the Database Logins first, instead of adding them to the Windows Logins List in Navision ?

Could this be a SID issue?

The effort here is all to get the USER table populated and insync with the Windows list. I'm doing this because our existing code/forms (pre-SQL conversion) make reference to lookup users in the USER table.

Perhaps this is wrong? Should all code/form references be changed to reference the Windows Login table instead? No, I don't think so.

Note. We are also running two companies in this database.

ecarmody

Actaully, update and correction.

We do not have to "pre"-create the account on SQL server first. I just go into Navision Windows Logins and add the userid there. The userid is validated to the domain and the entry is saved in the logins.

Then when I go back to SQL server, I see the new userid has been created and assigned to the Navision database.

Still can't get userid over to Database logins, however. And I cannot create them first in Database logins because it says they do not exist on SQL server.

bbrown

With Database Users you need to create the SQL Login accounts first. Then add them to NAV.

ecarmody

I've tried that too.

If I create a SQL Server Login type (account name/password) then I can go and add them on the Navision Database Login screen and it is valid.

However if I create the SQL account as Windows NT Authentication, then I cannot added them to the Database Login screen in Navision. I get the "SQL server user id does not exist" error.

Am I not understanding the correct usage of the Windows Login and the Database Login lists?

Question. If we are using Windows Authentication for our Navision user logins, do/should both the Windows Login list and the Database Login list be populated and in sync?

The repeat the issue, our USER table does not contain all the Windows Login user id's, and that is what I am trying to achieve.

bbrown

They are two different types of users. In SQL you can have users that use either "Windows Authentication" or "Database Authentication". The users setup as "Windows Authentication" go in the "Windows Logins" table in NAV. The users setup as "Database Authentication" go in the "Database Users" table in NAV.

ecarmody

Alright, that makes sense.

The Windows Logins are stored in system table "Windows Login", whereas the Database Logins are stored in system table "User".

Then what is the point of the Synchronize?

ecarmody

Furthermore, since our code/forms reference lookups in the User table, then since we've migrated from native Navision database over to SQL database, is that process support to include making code/form changes to anywhere that system table "User" is referenced to be changed to reference the "Windows Login" table instead?

kriki

[Topic moved from Navision Attain forum to SQL General forum]

bbrown

ecarmody wrote:

Alright, that makes sense.

The Windows Logins are stored in system table "Windows Login", whereas the Database Logins are stored in system table "User".

Then what is the point of the Synchronize?

Synchronization updates the SQL Application Role(s)

bbrown

ecarmody wrote:

Furthermore, since our code/forms reference lookups in the User table, then since we've migrated from native Navision database over to SQL database, is that process support to include making code/form changes to anywhere that system table "User" is referenced to be changed to reference the "Windows Login" table instead?

In newer versions there is Codeunit 418 "Login Management" for functions that check both user types. I'm not sure this existed in 3.60. I don't have a 3.60 DB handy to check. Maybe someone can verify.