reporting user is disabled in SQL Server after synchronizati

richsara

Related post: (~ref "Recommended security setup for SQL and ODBC?"~ http://www.mibuso.com/forum/viewtopic.p ... highlight= )

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
Rich Sara
Contract Programmer\Analyst~Solution Developer
NER Data Products, Inc
Corp. Email: <!-- w --><a class="postlink" href="http://www.Rsara@Nerdata.Com"&gt;www.Rsara@Nerdata.Com</a><!-- w -->
LinkedIn Profile: http://www.linkedin.com/in/richsara
Work Phone 888-637-3282 Ext 132
Cell Phone 856-627-5961
Office Fax 856-2393

richsara

Colleagues; a quick addendum:
We can log in to the network and Nav w\the user id. After having added the Nav user to role "Super (data)" the problem is STILL not resolved.
Cheers, RDS

richsara wrote:

Related post: (~ref "Recommended security setup for SQL and ODBC?"~ http://www.mibuso.com/forum/viewtopic.p ... highlight= )

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
Rich Sara
Contract Programmer\Analyst~Solution Developer
NER Data Products, Inc
Corp. Email: <!-- w --><a class="postlink" href="http://www.Rsara@Nerdata.Com"&gt;www.Rsara@Nerdata.Com</a><!-- w -->
LinkedIn Profile: http://www.linkedin.com/in/richsara
Work Phone 888-637-3282 Ext 132
Cell Phone 856-627-5961
Office Fax 856-2393

ara3n

Are you on standard or enhanced security?

richsara

A bit more background

1: the reoprting user is setup in the NAV system; I can log in and access Nav functions
2: We use windows integrated security ... This user, RE ODBC access, uses SQL Server authentication
3: We use standard security model
4: Nav 50, SP1 ; SQL Server 2005, SP2
5: Nav roles are "Super (Data)"
6: the roles (mapping) the sql server user s\have are "db_datareader", "public"

ara3n

Have you tried to remove the user from Nav?

richsara

Yes, actually, the 1st week we implemented the reporting user only in SQL Server (when the problem 1st occurred.) The entry into Nav security was done to attempt problem resolution.

ara3n

Is the user an Orphaned db user or a Server user?

I would try and delete the User add it again to server and see if it keeps disabling it.

rodbernardo

Hi Richsara,

We got the same issue, have you resolved it?

Appreciate if you coud share with us.

Thanks in advance,

Rodel

richsara wrote:

Related post: (~ref "Recommended security setup for SQL and ODBC?"~ http://www.mibuso.com/forum/viewtopic.p ... highlight= )

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
Rich Sara
Contract Programmer\Analyst~Solution Developer
NER Data Products, Inc
Corp. Email: <!-- w --><a class="postlink" href="http://www.Rsara@Nerdata.Com"&gt;www.Rsara@Nerdata.Com</a><!-- w -->
LinkedIn Profile: http://www.linkedin.com/in/richsara
Work Phone 888-637-3282 Ext 132
Cell Phone 856-627-5961
Office Fax 856-2393

richsara

Most certainly will ...

I have two issues; 1st is how to set a reporting user on SQL server properly, I'm certain it is not yet ideally configured. The second issue is the drop during Nav synch. I will revisit shortly and will keep everyone in the loop. However, if anyone finds any white papers on these two topics, please feel free to share them here. Thanks to all contributors, as always .
Cheers, RDS

kine

What I know, there is just one issue with the users with permissions set directly on SQL - they must be inserted into NAV as users (DB or Windows), but they do not need any permissions in NAV. After they exists in NAV,NAV will not drop their permissions on MS SQL. But this was already written...

richsara

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

ara3n

It's great that this was resolved, and thanks for the followup and detail steps.

richsara

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
_________________
Rich Sara
Contract Programmer\Analyst~Solution Developer
<!-- w --><a class="postlink" href="http://www.Rsara@Nerdata.Com"&gt;www.Rsara@Nerdata.Com</a><!-- w -->
LinkedIn Profile: http://www.linkedin.com/in/richsara
Work Phone 888-637-3282 Ext 132

rodbernardo

Hi ara and rich,

It's works here!!!

I delete and re-create the sql authentication user for reporting, then at the Navision I gave the user with super and super (Data) permission.

I keep monitoring the user permission in sql - using sp_helprotect, no issue since last night and I made manual manual multiple syncronization from Navision, but the permission didn't drop at all!

Thank you guys for the help!!!

Rodel

richsara wrote:

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
_________________
Rich Sara
Contract Programmer\Analyst~Solution Developer
<!-- w --><a class="postlink" href="http://www.Rsara@Nerdata.Com"&gt;www.Rsara@Nerdata.Com</a><!-- w -->
LinkedIn Profile: http://www.linkedin.com/in/richsara
Work Phone 888-637-3282 Ext 132

richsara

Most excellent! Thanks for collaborating, all!

rodbernardo wrote:

Hi ara and rich,

It's works here!!!

I delete and re-create the sql authentication user for reporting, then at the Navision I gave the user with super and super (Data) permission.

I keep monitoring the user permission in sql - using sp_helprotect, no issue since last night and I made manual manual multiple syncronization from Navision, but the permission didn't drop at all!

Thank you guys for the help!!!

Rodel

richsara wrote:

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
_________________
Rich Sara
Contract Programmer\Analyst~Solution Developer
<!-- w --><a class="postlink" href="http://www.Rsara@Nerdata.Com"&gt;www.Rsara@Nerdata.Com</a><!-- w -->
LinkedIn Profile: http://www.linkedin.com/in/richsara
Work Phone 888-637-3282 Ext 132