Skip to end of metadata
Go to start of metadata

PURPOSE

Document known login related issues to ease the task of troubleshooting for the community members and customers.

OVERVIEW

An issue belongs to this topic, if it is related with SAP user login, SAP user authentication, user schema mapping or permissions.

MONITORING INFORMATION:

Work process trace

Work process traces can be found in the following directory:

[Drive]:\usr\sap\[SAPSID]\DVEBMGS00\work\dev_w*

TABLE OF CONTENTS

KNOWN ISSUES:

1 .  User-schema mapping issue

Symptom:   
The following errors appear in the dev_w?? file(s) in the work directory:
=====================================================================
C  ExecuteAndFlush return code: 0x80040e14 Stmt:   [if user_name() != 'prd' setuser 'prd']
C  ExecuteAndFlush: line 36329. hr: 0x80040e14 There is no such user or    or group 'prd'.
C  HandleOledbError [ExecuteAndFlush,line 36329]: Error/Message: (err    4604, sev 0), There is no such user or group 'prd'.
C  setuser 'prd' failed -- connect terminated
=====================================================================

Solution:

551915 - R/3 won't start after database restore or database copy
The schema repair script can be found in the SAP Note:
1294762 - SCHEMA4SAP.VBS

2 .  The SELECT permission was denied on the object 'BC_COMPVERS'

Symptom:   
The following errors appear in the dev_w?? file(s) in the work directory:
...
B  Connect to SID as SID_SAPMonitor with OBJECT_SOURCE=SAPMonitor MSSQL_DBNAME=SID MSSQL_SERVER=SERVER1234
C  Thread ID:4320 ...
C  ERROR: -1 in function StartSelect (execute) [line 13380]
C  (229) [42000] [Microsoft][SQL Server Native Client 11.0][SQL Server]The SELECT permission was denied on the object 'BC_COMPVERS',
C  dbdsmss: DBSL99 SQL229
C  The SELECT permission was denied on the object 'BC_COMPVERS', database 'SID', schema 'SAPSIDDB'.
B  ***LOG BY2=> sql error 229        performing OPC        [dbds         398] 
... 

Solution:

Start the following script:

use [SID] 
GO
GRANT SELECT ON [SAPSIDDB].[BC_COMPVERS] TO [SAPMonitor]
GO 
          

3 .  SAP Host Agent Login needs sysadmin role, but from security reason it is not allowed

Symptom:  
Based on the SAP Note 1877727 sysadmin role is necessary to add to login user NT AUTHORITY\SYSTEM. However the customer related security guide of the local system does not allow to add this sysadmin role.

Solution:

Based on the SAP Note:
1877727 - sapdbctrl: not member of sysadmin

It's possible to run sapdbctrl with a system account and the associated SQL logon NT AUTHORITY\SYSTEM not being member of the Server Role sysadmin. In that case the functionality of sapdbctrl is reduced (i.e. detach/attach is not supported anymore). But this is not considered anymore an error.

4 .  Login failed for user '[DOMAIN]\[user]'

Symptom:  
Similar error message appears in workprocess trace or transport log:
...
[Microsoft][SQL Server Native Client 11.0][SQL Server]Login failed for user '[DOMAIN]\[user]'
...          

List of related issues:

4.1. The error is comming during SAPInst

Symptom:  
The error is coming during SAPInst by command R3load or R3trans for sidadm user.

Common suggestions:

    • Check the SQL Error Log

Description:  
Check the SQl Error Log for more information.

    • Login to SQL Server Management Studio

Description:  
Check in SQL Server Management Studio that the user [DOMAIN]\sidadm has login access to the SQL Server.

    • Check sqlcmd login

Description:  
Check sqlcmd login based on the following wiki page:

SQL Server Native Client

4.2. Logon to Management Studio Error 18456

Symptom:  
When connecting to Microsoft SQL Server with the Microsoft SQL Server Management Studio, and you are using Windows authentication, a logon error occurs:

 "Login failed for user '[username]'. (Microsoft SQL Server, Error: 18456" 

The error can also occur when using the command line tools such as osql or sqlcmd. Then the error message looks like this:

"Msg 18456, Level 14, State 1, Server WSI6463REX\REX, Line 1 
Login failed for user 'SAP_ALL\i002675'."


 The SQL Server error log shows an error like this:

"Login failed for user '[user]'. Reason: Token-based server access validation failed with an infrastructure error. Check for previous errors."                

Solution:

1386328 - Logon to Management Studio Error 18456

 

4.3. Java AS user login problem

Symptom:

Several java tools or processes, e.g. the visual administrator or the config tool, are not able to connect to the database. 

The SQL Server Error logs show: 

Login failed for user 'SAP[SID]DB'.
Reason: An attempt to login using SQL authentication failed. 

Solution:

1743072 - MSSQL: Login for java stack user SAPDB fails 
or 
1950926 - System cannot start due to login SQLServerException 

4.4. Login failed after applying Netweaver 7.0 SAP_BASIS SP31

Symptom:

After applying Netweaver 7.0 SAP_BASIS support package 31, sap system cannot connect to remote SQL Server. 

 In developer trace, the following error happens: 

Error/Message: (err 18456, sev 0), Login failed for user 'XXXX'. 

Solution:

2193929 - Remote SQL Server cannot be connected after applying support package. 

4.5. Login failed after installing Solution Manager Diagnostic Agent

Symptom:

After installing Solution Manager Diagnostic Agent, the following error happens on SQL Server ERRORLOG. 

Error: 18456, Severity: 14, State: 11 Login failed for user '[Domain]\[Hostname]$'.  
Reason: Token-based server access validation failed with an infrastructure error. 
Check for previous errors. [CLIENT: XX.XX.XX.XX] 

Solution:

1905498 - Token-based server access validation failed with an infrastructure error. 

 

COMMON ANALYSIS STEPS AND SOLUTIONS:

1 . Check the login with Management Studio

Description:

Check the login with the SQL Server Management Studio. 

Implication:

If the login fails, check the details of the error message. 

2 . Check if there is a bug in DBSL

Description:

Check if there is a bug in the DBSL module. 

Implication:

If issue found as a bug, apply the SAP Note or apply the DBSL patch, which contains the bugfix.

NOTE/KBA SEARCH:

In case that the above solutions are not relevant, the following search keys are suggested for SAP Note and KBA search:

Component area: BC-DB-MSS*
Search keys:
- "Login failed"
- User Login

 

RELATED SAP NOTES/KBAs


SAP Note 1645041 - FAQ: Microsoft SQL Server logins and their usage in SAP environment
SAP Note 1294762 - SCHEMA4SAP.VBS
SAP Note 551915 - R/3 won't start after database restore or database copy
SAP Note 592514 - MSSQL: internal database users, permissions and security 

RELATED CONTENT


N/A

Feedback

Please provide any feedback or questions regarding the content under the current page comments. If you have an issue or is looking for help, try to post your question on SAP community with Microsoft SQL Server tag.

In order to leave comments, you must be logged on with your SAP account.