Cannot syncronize SQL to

mrecht

We recently upgraded from NAV 5.01 on a native database to NAV 2009 on a SQL database using the Classic client.

I setup the database users in SQL as I always have for NAV 5, but when I try to “Synchronize All Logins” I receive the following error in spite of that login already existing in SQL and being associated with the database:

The Microsoft Dynamics NAV Classic and SQL Server security systems have not been successfully synchronized.
The SQL Server login TEMP does not exist on the NAVSRV server.

SQL 2005 is updated to SP 3, and I have already tried giving user TEMP db_owner access.

I cannot add new users or update SQL permissions/password until this is fixed.
What is causing this synchronization error?

bbrown

Does a SQL Server login (not to be confused with a DB User) exist for TEMP?

ara3n

what user is the creator of the db?

Make sure it's sa.

kriki

Small advice: if you don't need the enhanced security model (you need it if users need to access the NAV-DB, using NON-NAV means), it is best to change the standard security model. In this way you don't need to synchronize.

ssingla

kriki wrote:

Small advice: if you don't need the enhanced security model (you need it if users need to access the NAV-DB, using NON-NAV means), it is best to change the standard security model. In this way you don't need to synchronize.

Can you explain in more detail about how to set this standard security model setting.

kriki

1) put your DB in single user mode (File=>Database=>Alter=>Tab Options => "Single User"=Yes and confirm. Everyone (including NAS's) should leave the DB.
2) change the security model (File=>Database=>Alter=>Tab Advanced=>"Security" => "Model" => Standard and confirm. This can take some time
3) Put your DB back to multi-user mode (File=>Database=>Alter=>Tab Options => "Single User"=No and confirm.

PS (for completeness): this is only possible from 4.0SP3 and up.

mrecht

How long should it take for a database (500MB) to switch to single-user mode?

I started the process and it lasted for over two hours, so I had to cancel to make sure we were functional.

bbrown

mrecht wrote:

How long should it take for a database (500MB) to switch to single-user mode?

I started the process and it lasted for over two hours, so I had to cancel to make sure we were functional.

Are you sure no one else was in the database? It will sit there forever if there is.

ara3n

mrecht wrote:

How long should it take for a database (500MB) to switch to single-user mode?

I started the process and it lasted for over two hours, so I had to cancel to make sure we were functional.

I usually do this from SQL SMS and there is an option to kick existing people out when doing it.