production dbase user mapping is disabled in SQL Server after synchchronizing Navision users

Normal 0 false false false EN-US X-NONE X-NONE MicrosoftInternetExplorer4

Folks;

We have configured a reporting user on SQL server (NavReportingUser ) which has data read access only.  This profile is the profile we are using to enable SQL Server RS reporting over Navision data. It’s a best practice approach. The problem we have is that the user mapping for this user is disabled in SQL Server after synchronizing Navision users in production.

One of our var consultants says that "he has brought it up with Microsoft but does not expect a fix anytime soon. If you synchronize users through Navision it will remove the database mapping for any user not configured in Navision for that database. For example, if you have NavReportingUser setup as a login and then it is a user in the PRODUCTION database. You must configure the NavReportingUser inside the Navision database either as a windows or database login."

Well, we have set the profile up in the network and set it up in Navision security as well. The problem is not resolved. What other steps might we need to take? Is there a white paper that you are aware of that we can refer to? Any help is appreciated.

 

Peace, Rich Sara

Are you assigning NAV permissions to this user?  Are they setup as public?  This users permissions should be goverened through NAV permissions.  

Give them the public role and in NAV give the user the role of SUPER (DATA).

NAV will then assign SQL read only permissions to that user. 

You could Enhanced security model to overcame that, but you would have to create a matching Navision user and having a longer synchronization process.

 

Joshi;

 Thx for  the suggestion. I added the user to the Super (data) group,  In sql,he has public and db_datareader only. I can log on and access the profile in NAv, so I'm not sure what that tells me.

Anyway, it's still having its dbase user mapping disabled after every user security synch. 

 Cheers, RDS

Normal 0 false false false EN-US X-NONE X-NONE MicrosoftInternetExplorer4

 Colleagues;

I am pleased to report to you that I have apparently resolved this troublesome issue. The steps I took are as follows:

SQL Server steps:

1:  A new login was created in SQL Server with no domain specification using SQL Server authentication
2: A password was used, but password policy and expiration were disabled
3: User mappings were set on the target dbase (“production” in this case). User is the new login, default schema was the login id. Role memberships were set to public, db_datareader
4: Status settings are: Permission to connect to dbase engine = “Grant”, Login=”Enabled”

Navision security steps:

1: A new database login was created for the new login id
2: A single role “SUPER (DATA) “ was assigned to the login

Testing:

Multiple (at least 5) resynchs did not alter the dbase mappings. An external ODBC system data source was tested using the new login against SQL and it ran fine. Our shared production data source used by our SQL RS reports was updated, tested, and re-deployed to the report server, all reports ran fine.

Thanks for everyone’s help, I think that we can (hopefully) put this issue to rest.

Cheers! RDS