This document is provided AS-IS and is intended to assist in understanding the security structure of the SL 7.0 databases. While feedback is welcome on this document, errors or omissions do not constitute a product defect.
The information in this document comes from observing the database structure in SQL Management Studio and from running traces of various processes in SQL Profiler. No information derived from compiled source code is included.
Unless otherwise stated, the information in this document is assuming the use of “Windows Authentication”.
Contents:
- SQL Users and Roles
- 07718158D19D4f5f9D23B55DBF5DF1
- E7F575915A2E4897A517779C0DD7CE
- MASTER60SP
- CD7359B5576446f85EB67E824B4770
- MSDynamicsSL
- MSDSL
- DOMAIN\username
- BusinessPortalUser
- BFGROUP
- SL Groups
- Administrator
- Everyone
- The Synchronize All Ownership & Security Update Scenario
- Database Owner
- Miscellaneous
- SQL Server Service Account
- ODBC Connections
- The "Trustworthy" property on the database
- Adding users in the User Maintenance screen
- Moving databases to a new server
- SL 6.5 Security Diagram - see https://sharepoint.partners.extranet.microsoft.com/sites/LegacyUploaded_BemisRepository/Bemis/BemisUploadedFiles/BemisDocuments/22/bemisdocuments/2001245_en-US.docx
- SL 7.0 Security Diagram - see https://sharepoint.partners.extranet.microsoft.com/sites/LegacyUploaded_BemisRepository/Bemis/BemisUploadedFiles/BemisDocuments/22/bemisdocuments/2001245_en-US.docx
SQL Users and Roles:
07718158D19D4f5f9D23B55DBF5DF1 - Also known as the “The SuperUser Entity”. This account should show as disabled. This login is created by Database Maintenance as “non-interactive”, which means you cannot login with this login account. It is only used for impersonation within SL.
This user id is used when a single stored procedure needs to access objects in the SL System and SL Application database. The pp_cleanwrkrelease stored procedure is a good example. This proc runs a delete statement from a table in the SL Application database based on information in the Access table in the SL System database. (The stored procedure gets to the Access table by using the VS_Access view in the app database). So within the proc, there is a statement that looks like this:
WITH EXECUTE AS '07718158D19D4f5f9D23B55DBF5DF1’
This indicates that instead of using our windows login which gets its rights from the MSDSL application role, we are going to impersonate the 07718158D19D4f5f9D23B55DBF5DF1 user when running this one proc. This is needed because SQL application roles (like MSDSL) don’t work across databases. If the “With Execute” impersonation was not used, we would get the following error:
---------------------------
SQL Server Message 916
---------------------------
The server principal "DOMAIN\username" is not able to access the database "SLSYS" under the current security context.
---------------------------
OK
---------------------------
The 07718158D19D4f5f9D23B55DBF5DF1 user has access to most of the vs_ views and a few of the tables in the SL application database and most of the tables in the SL System database. This 07718158D19D4f5f9D23B55DBF5DF1 user or the impersonation also relates back to the database owner due to ownership chaining. The owner of the SL System and SL Application databases need to match… if they don’t, then you will get this error:
---------------------------
SQL Server Message 916
---------------------------
The server principal "07718158D19D4f5f9D23B55DBF5DF1" is not able to access the database "SLSYS" under the current security context.
---------------------------
OK
---------------------------
It doesn’t seem to matter who the database owner is as long as all of your SL Application and SL System databases have the same owner. See “Database Owner” section for more information. See Also http://msdn2.microsoft.com/en-us/library/ms188676.aspx for more information on ownership chaining.
E7F575915A2E4897A517779C0DD7CE - Also known as “The Report User”. This user ID is used alongside the ODBC connection and your windows user ID to run Crystal Reports. The user seems to have SELECT rights to all tables and views and EXECUTE rights to all stored procedures in the SL System and SL Application databases.
The user will need to have SELECT/EXECUTE rights to any custom objects used in a custom report or you will get the following error:
---------------------------
Crystal Reports Helper Application for Solomon IV
---------------------------
Get SQL Query failed
Report: C:\Program Files\Microsoft Dynamics SL\Usr_Rpts\03730DET.RPT
Crystal Print Engine Error: 709 - Error in File C:\Program Files\Microsoft Dynamics SL\Usr_Rpts\03730DET.RPT:
The table could not be found.
---------------------------
OK
---------------------------
If the account is disabled or if the account’s password was changed, you will get the following error when running any report:
---------------------------
Crystal Reports Helper Application for Solomon IV
---------------------------
Get SQL Query failed
Report: C:\Program Files\Microsoft Dynamics\SL\Applications\GL\01650.RPT
Crystal Print Engine Error: 536 - Error in File C:\Program Files\Microsoft Dynamics\SL\Applications\GL\01650.RPT:
Unable to connect: incorrect log on parameters.
---------------------------
OK
---------------------------
MASTER60SP - The master user from the SL 6.0 SP1 - SL 6.5 Security Model. This user is still used if running SL 7.0 under “SQL Authentication”. In a windows authenticated database, this user does not typically exist and is not used. The user is roughly the equivalent of the “Master” user in SL 6.0 and older.
In a non-windows authenticated database, the master60sp user is the owner of the SL Application and SL System database, giving it full control over all the objects in those databases. All interaction between the SL screens and reports is done though the master60sp user… the windows user ID is not used at all. The password of this user can be changed by going into the Database Administration (98.270.00) screen in SL.
The user is also still used in FRx. This can be a problem because in a windows authenticated database, the master60sp user doesn’t exist. KB 941591 explains how to get around this.
CD7359B5576446f85EB67E824B4770 User - The “prelogin” user from the SL 6.0 SP1 - 6.5 Security model. This user is also used if running SL 7.0 under “SQL Authentication”. In a SL 7.0 windows authenticated database, this user does not typically exist and is not used. The user is roughly the equivalent of the “MasterRO” user in SL 6.0 and older.
The user only has access to the system database. It should NOT have rights to the application database or you can get the issue described in KB 896321. It can select from several tables and execute several stored procedures. It can also insert/update/delete in the domain and rptextra tables.
During the logon process, the CD7359B5576446f85EB67E824B4770 user performs the following tasks:
- Verifies the version of SQL
- Verifies the version of the SL Database
- Checks for registration keys
- Gets a list of companies in the database
- Hands the processing off to the master60sp user
MSDynamicsSL Database Role - All SL users are a member of this role. The role in the SL System database has Execute permissions to the getAuthenticationType, GetInfo, and getVersion stored procedures on the system database. The role in the SL application database has no permissions. It is not known what purpose this role serves in the SL Application database. If users are not a member of the MSDynamicsSL database role on the SL System DB OR if the MSDynamicsSL database role on the SL System does not have EXECUTE permission to the getauthenticationtype stored procedure, then SIGNON.EXE will crash when non-administrators attempt to login to SL.
MSDSL Application Role - This role has Control rights to all objects in the database.
The importance of this being an “application” role instead of a normal “database” role is that the rights assigned are only valid when accessing the database from the SL application. So even though the user might have rights to add a new account to the Chart of Accounts Maintenance screen inside SL, they will not have rights to run an INSERT command on the ACCOUNT table while in SQL Server Management Studio.
The role is database specific… meaning the MSDSL role in one database is not the same role as the MSDSL role in another database even though they are named the same thing. The “MSDSL” role in the SL application database has control rights over the objects in that database. And the “MSDSL” role in the SL system database has control rights over the objects in the system database.
If the password on this role is incorrect, it can cause the following error during signon (the synchronize ownership and security scenario will correct this UNLESS there are multiple SL system databases that point to the same application database):
---------------------------
SIGNON
---------------------------
Fatal SQL Error 15161 Occurred during Company Login
---------------------------
OK
---------------------------
If the sync scenario does not correct the error, then you should manually go through every database on the SQL server and run the following to see if there happens to be multiple system DBs pointing to the same app DB. If you find this to be the case, one of the system DBs will need to be removed or at least unliked from the app DB.
select
databasename, * from domainselect databasename, * from company
DOMAIN\username - Another change in 7.0 is that the user’s windows logins are now added to the SQL Database. You can see this in SQL Management Studio by expanding on one of the SL Databases -> Security -> Users. The user accounts don’t have any of their own access rights; they get a few rights by being a member of the MSDynamicsSL role, but the rights are primarily granted by SL using the MSDSL application role. The exception is if the user is a member of the SL Administrators group. See the discussion in “SL Groups” below for more info.
By having the user accounts in SQL, the users will have permission to login to SQL Server Management Studio without having to know the “sa” password. Luckily because application roles are being used, they will not have permission to actually do anything once logged in.
BusinessPortalUser - This SQL Server user is created when installing Business Portal. It is used for interaction between business portal and SQL. It has no rights of its own. It gets its power from being a member of the BFGROUP database role.
BFGROUP Database Role - This role is used to grant “SELECT, UPDATE, INSERT, DELETE” permissions to all SL objects. The BusinessPortalUser should be the only member of this role. Occasionally the role does not have the correct access rights to various objects which can cause errors in business portal. For more information, see KB 906715.
SL Groups:
Administrators group - As the name implies, being a member of this group gives the user administrative privileges in SL. Any member of the group becomes the equivalent of the “SYSADMIN” user in prior versions. Individual rights are not assigned to the “administrators” group in the Access Rights Maintenance screen… instead being a member of this group automatically gives full rights to all screens and reports.
Being a member of the Administrators group also gives some additional privileges:
- Only administrators will see the standard “Administration” module group in the menu
- Only administrators can add new users to the system
- Administrators are also automatically given the “sysadmin” server role on the SQL Server. This is important to know because the user will now be able to log into SQL Server Management Studio and perform any task on any database.
- Note: Up to debate whether this is improved/changed in 7.0 Fp1 with the "grant this user permission to create SQL server logins and users"
Because of this, membership in this group should be limited to only those users who absolutely need it.
Everyone group - Contrary to what the name implies, all users are not automatically a member of the “everyone” group. You must manually add new users to the Everyone group. By default, this group is used to provide users with the standard menu. The group does not provide any access rights by default.
The Synchronize All Ownership & Security Update Scenario: This scenario in Database Maintenance (98.290.00) can correct a variety of issues and should be the first step in troubleshooting ANY security or login related issue. When the scenario is run, it syncs ALL the databases on the server regardless of which one is selected in database maintenance. Here are the specifics on what the process does: · Windows Authenticated Databases o Sets the DB Owner § In 7.0 the Owner will be set to the login ID used to log into database maintenance. § In 7.0 SP1 the owner is set to ‘sa’ o Creates the 07718158D19D4f5f9D23B55DBF5DF1 and E7F575915A2E4897A517779C0DD7CE users on the SQL Server only if they are missing. o Drops then Re-adds the E7F575915A2E4897A517779C0DD7CE user from the SL System and SL Application databases. o Grants rights to the E7F575915A2E4897A517779C0DD7CE user. o Grants rights to the 07718158D19D4f5f9D23B55DBF5DF1 user. o Sets the trustworthy property on the SL System and SL Application databases to TRUE. o Creates the MSDynamicsSL database role if it is missing and assigns it rights, but does not re-add the users to the role. See bug 15135. § In SL 7.0, it only re-creates the role on the SL System database § In SL 7.0 Sp1, it recreates the role on SL System and SL Application databases. o Creates the MSDSL application role on the System and Application DBs only if they are missing o Assigns rights to the MSDSL role in the system DB. Due to bug 15053, rights are not assigned to the MSDSL role in the app db if the role was missing. This can lead to a System Message 10232 at login. See bug for workaround. o Resets and Syncs the MSDSL app role password. o Resets the E7F575915A2E4897A517779C0DD7CE user password. o Resets the 07718158D19D4f5f9D23B55DBF5DF1 user password. · SQL Authenticated Databases o Sets the Owner of the SL System and SL Application databases to master60sp. o Creates the master60sp user on the server if missing. o Creates the CD7359B5576446f85EB67E824B4770 user if missing. o Drops then Re-adds the CD7359B5576446f85EB67E824B4770 user from the SL System and SL Application databases. o Grants rights to the CD7359B5576446f85EB67E824B4770 user. o Sets the trustworthy property on the SL System and SL Application databases to TRUE. o Syncs the master60sp password. o Resets the CD7359B5576446f85EB67E824B4770 user password. Database Owner: The owner of the database gets set during database maintenance (98.290.00). In version 6.0 Sp1 – 6.5 or in SL 7.0 using SQL Authentication, the owner of the database is master60sp. In SL 7.0, the owner of the databases will get set to the user ID provided to login to database maintenance. In SL 7.0 Service Pack 1, the owner of the databases will get set to sa. The owner of the database inherits full control over all objects within the database. It doesn’t typically matter who the owner of the databases are as long as the same user owns all SL databases. There is an exception to this. If the owner of the database is a domain user and for some reason the SQL Server has trouble contacting a domain controller, you may see the following error in a variety of places: --------------------------- In order to become the owner of the database, you cannot already be a user in that database. This has the potential to cause the following error in Database Maintenance: --------------------------- See KB 942450 for more info on this error. To avoid this, it is recommended to make the owner of the databases a SQL user such as “sa” (which happens in 7.0 Service Pack 1 automatically) Miscellaneous: SQL Server Service Account. The service account can be any account but it must have READ permissions to the user account objects in active directory. Otherwise you will get the following error when trying to add users to SL: --------------------------- The SQL Server Service Account can be set by going to Start -> Settings -> Control Panel -> Administrative Tools -> Services. Scroll down to “SQL Server (MSSQLSERVER)”, right click and select Properties. Then click the Log On tab. ODBC Connections. When you first run a crystal report on a new workstation, an ODBC connection is created for the SL System and SL Application databases in the User DSN tab of Data Sources (ODBC). This connection should be set to use SQL authentication even if you use Windows Authentication to login to Dynamics SL. If this connection is changed to use windows authentication OR a System DSN is added and uses windows authentication, users may see the following error: --------------------------- It is safe to delete the User DSN entries as they will automatically be re-created. You can view the entries by going to Start -> Control Panel -> Administrative Tools -> Data Sources (ODBC). The “Trustworthy” property on the database. This appears to be something new in SQL 2005. When a database is first attached to a SQL Server, the “trustworthy” property on the database is set to FALSE. This means that any objects in the database that attempt to access objects in another database (such as the vs_company view in the SL Application database) will fail. This can lead to the following error in a variety of places including just opening screens in SL: --------------------------- You can see the current status of the property by going to SQL Server Management Studio -> Right click on the database -> select properties -> click Options. The Trustworthy property is under the Miscellaneous section. The Synchronize All Ownership & Security database maintenance scenario appears to set this to TRUE for all SL databases. More information can be found here: http://msdn2.microsoft.com/en-us/library/ms187861.aspx Adding users in the User Maintenance screen. Even if a user has update, insert, delete rights to the user maintenance screen (98.260.00) they still cannot add new users or add users to the Administrators group. When you try to enter the Windows Username and tab off, you will receive the following error: --------------------------- If you try to add a user to the Administrators group, you will receive the following error: --------------------------- You must be in the SL Administrators group to add users or add users to the Administrators group. When you add a new user ID, the process is actually adding the windows user as a new SQL Server user. This requires an elevated level of SQL Rights that the MSDSL role alone does not grant. So you must be in the SL Administrators group. Moving databases to a new server. In a windows authenticated database, every users windows login ID are added to the SQL Server under SQL Server -> Security -> Logins. Those logins are then added to the database under SLDATABASE -> Security -> Users. But if you backup a database and restore it to a new server, the login IDs are not automatically added to the SQL Server. So now the login IDs in the database are orphaned. See bug 15024 which should be corrected in 7.0 FP1. To correct this, you must run the following script against the SL System DB on the new server. If you do not do this, users will get “SIGNON.EXE has encountered a problem and needs to close. Sorry for the inconvenience” errors when trying to login declare @windowsuseracct as char(85)
SQL Server Message 15404
---------------------------
Could not obtain information about Windows NT group/user 'DOMAIN\username', error code 0x54b.
---------------------------
OK
---------------------------
9829000
---------------------------
SetOwner Error -2147206394: [Microsoft][ODBC SQL Server Driver][SQL Server]The proposed new database owner is already a user or aliased in the database.
---------------------------
OK
---------------------------
SQL Server Message 15404
---------------------------
Could not obtain information about Windows NT group/user 'DOMAIN\username', error code 0x54b.
---------------------------
OK
---------------------------
Crystal Reports Helper Application for Solomon IV
---------------------------
Get SQL Query failed
Report: C:\Program Files\Microsoft Dynamics\SL\Applications\GL\01720.RPT
Crystal Print Engine Error: 709 - Error in File C:\Program Files\Microsoft Dynamics\SL\Applications\GL\01720.RPT:
The table could not be found.
---------------------------
OK
---------------------------
SQL Server Message 916
---------------------------
The server principal "07718158D19D4f5f9D23B55DBF5DF1" is not able to access the database "SLSYS" under the current security context.
---------------------------
OK
---------------------------
SQL Server Message 229
---------------------------
The EXECUTE permission was denied on the object 'xp_logininfo', database 'mssqlsystemresource', schema 'sys'.
---------------------------
OK
---------------------------
SQL Server Message 15247
---------------------------
User does not have permission to perform this action.
---------------------------
OK
---------------------------
declare @execString as char(200)
DECLARE user_cursor CURSOR FOR
select distinct windowsuseracct from userrec
left join sys.server_principals slogins on userrec.windowsuseracct=slogins.name
where windowsuseracct<>'' and slogins.name is null
OPEN user_cursor
FETCH NEXT FROM user_cursor INTO @windowsuseracct
WHILE @@FETCH_STATUS = 0
BEGIN
set @execString = 'CREATE LOGIN ' + QUOTENAME( rtrim(@windowsuseracct) ) + ' FROM WINDOWS WITH DEFAULT_DATABASE = [master]'
exec (@execString)
FETCH NEXT FROM user_cursor INTO @windowsuseracct
END
CLOSE user_cursor
DEALLOCATE user_cursor