Resolving connection errors between WebSphere Application Server and SQL Server
Achieving a clean connection
SQL Server, Microsoft®’s enterprise-level relational database, is one of the more popular databases used with IBM WebSphere Application Server, as well as with IBM WebSphere Portal. If you’re using SQL Server with WebSphere Application Server, then you might be familiar with this common error that occurs when connecting from WebSphere Application Server to SQL Server 2008:
DSRA0010E: SQL State = S0001, Error Code = 18,456
DSRA0010E error means that WebSphere Application Server is reporting a database error. Error code 18456 within this error means it has to do with a failed login. To fix this problem in the database, you need to understand the possible causes and resolutions.
When using WebSphere Application Server, this problem can be caused by:
- Not using SQL Server authentication.
- Not using or using an incorrect login name.
- Not using, or using an incorrect or expired password.
Correcting this issue requires the WebSphere Application Server administrator to work with the SQL Server database administrator (DBA). This error is recorded both in the WebSphere Application Server SystemOut.log file and the log files of SQL Server 2008.
Before considering each of the three causes above in more detail, let's first look at some background to help you better understand why this happens at all.
WebSphere Application Server and SQL Server together
In WebSphere Application Server, the login name is called User ID. SQL Server 2008 and WebSphere Application Server differ on what is permitted for login name (User ID) and password. Table 1 shows these differences.
Table 1. Login name
|Login name||SQL Server 2008||WebSphere Application Server|
|No login name, no password||Not allowed||Allowed|
|Login name, no password||Allowed||Not allowed|
|Login name with password||Allowed||Allowed|
Both SQL Server 2008 and WebSphere Application Server recommend using both a login name (User ID) and a password for better security. In WebSphere Application Server, the User ID is stored as a J2C authentication alias. Figure 1 shows an example where the User ID is sqlserveruser, saved as the alias db1wasNode01/SAMPLE.
Figure 1. J2C authentication alias
The procedure to create a J2C authentication alias is described in the WebSphere Application Server Information Center.
You would then need to apply the above to your data source. To do this, navigate to your WebSphere Application Server admin console (Figure 2) by selecting Resources > JDBC > Data sources > $DATASOURCE_NAME > Security settings. Click OK and then Save to directly save to the master configuration.
Figure 2. Data source security settings
Notice that IBM recommends using both Component-managed and Container-managed authentication aliases. For more information about setting up SQL Server 2008 data sources, see the WebSphere Application Server V7 Administration and Configuration Guide.
Now, let’s review the three above problems and see how you can address them in SQL Server 2008.
Not using SQL Server authentication
If the SQL Server 2008 login name was not set up for SQL Server authentication, then you will see the error shown in Listing 1 in the SystemOut.log file:
[9/8/10 17:37:35:953 CDT] 00000016 DSConfigurati W DSRA8201W: DataSource Configuration: DSRA8040I: Failed to connect to the DataSource. Encountered java.sql.SQLException: Login failed for user 'sqlserveruser'.DSRA0010E: SQL State = S0001, Error Code = 18,456. java.sql.SQLException: Login failed for user 'sqlserveruser '.DSRA0010E: SQL State = S0001, Error Code = 18,456 at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError (SQLServerException.java:196) at com.microsoft.sqlserver.jdbc.TDSTokenHandler.onEOF(tdsparser.java:246) at com.microsoft.sqlserver.jdbc.TDSParser.parse(tdsparser.java:83) at com.microsoft.sqlserver.jdbc.SQLServerConnection.sendLogon (SQLServerConnection.java:2532) at com.microsoft.sqlserver.jdbc.SQLServerConnection.logon (SQLServerConnection.java:1929) at com.microsoft.sqlserver.jdbc.SQLServerConnection.access$000 (SQLServerConnection.java:41) at com.microsoft.sqlserver.jdbc.SQLServerConnection$LogonCommand.doExecute (SQLServerConnection.java:1917) at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:4026) at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand (SQLServerConnection.java:1416) at com.microsoft.sqlserver.jdbc.SQLServerConnection.connectHelper (SQLServerConnection.java:1061) at com.microsoft.sqlserver.jdbc.SQLServerConnection.login (SQLServerConnection.java:833) at com.microsoft.sqlserver.jdbc.SQLServerConnection.connect (SQLServerConnection.java:716) at com.microsoft.sqlserver.jdbc.SQLServerDataSource.getConnectionInternal (SQLServerDataSource.java:577) at com.microsoft.sqlserver.jdbc.SQLServerPooledConnection.createNewConnection (SQLServerPooledConnection.java:60) at com.microsoft.sqlserver.jdbc.SQLServerPooledConnection.<init> (SQLServerPooledConnection.java:42) at com.microsoft.sqlserver.jdbc.SQLServerConnectionPoolDataSource. getPooledConnection(SQLServerConnectionPoolDataSource.java:34) at com.microsoft.sqlserver.jdbc.SQLServerConnectionPoolDataSource. getPooledConnection(SQLServerConnectionPoolDataSource.java:24) at com.ibm.ws.rsadapter.DSConfigurationHelper$2.run (DSConfigurationHelper.java:1633)
The SystemOut.log file tells you there is an issue with login name sqlserveruser. To get more details on why SQL Server 2008 failed to login sqlserveruser, you can review the log files on SQL Server 2008. Your SQL Server 2008 DBA will need to launch the Microsoft SQL Server Management Studio, and expand Management > SQL Server Logs. This is shown in Figure 3.
Figure 3. SQL Server logs
Now, using the timestamp from the WebSphere Application Server
SystemOut.log file, such as
CDT, you can search
through SQL Server 2008 logs and find the corresponding log file entry
Figure 4. Log File Viewer
Notice that SQL Server tells why login failed for user sqlserveruser: it is because it was configured for Windows® authentication only. This will need to be corrected on SQL Server 2008. However, if a login name has been created to use Windows authentication, it cannot be modified to use SQL Server authentication or vice-versa (Figure 5).
Figure 5. Login Properties for an SQL Server user
The DBA will need to either delete and re-create the same login name or create a new login name. To create a new login name, WebSphere Application Server requires that the new login has:
- SQL Server authentication.
- A password.
- Membership of a group that has sufficient authority to issue TRUNCATE TABLE statements and have CREATE TABLE statement privileges.
- Privileges to connect to the database.
Figure 6 illustrates how to create an SQL Server 2008 login name. In Microsoft SQL Server Management Studio, you will need to expand Security > Logins, then right-click on Logins, then select New Login.
Figure 6. Create new login
Enter the Login name. You will notice that the default is Windows authentication. Change this to SQL Server authentication, then enter the Password twice (Figure 7).
Figure 7. Enter login parameters
Select the User Mapping page and map to the database you wish to connect to; in this example, the SAMPLE database is used (Figure 8). Click OK to save.
Figure 8. Define user mapping
Not using or using an incorrect login name
As mentioned above, SQL Server 2008 requires a login name but not a password to login (Figure 9).
Figure 9. Login name required to connect to SQL Server
However, when you attempt to connect to SQL Server 2008 from WebSphere Application Server without a login name (User ID), you will get this error:
[10/27/10 12:38:14:441 CDT] 00000016 DSConfigurati W DSRA8201W: DataSource Configuration: DSRA8040I: Failed to connect to the DataSource. Encountered java.sql.SQLException: Login failed for user ''.DSRA0010E: SQL State = S0001, Error Code = 18,456.
Notice that login failed for
user '' (in other words, no login
name is specified). Refer to the previous section for details on creating
a login name.
If you use the wrong login name, you will get this error:
[10/28/10 11:23:24:097 CDT] 00000014 DSConfigurati W DSRA8201W: DataSource Configuration: DSRA8040I: Failed to connect to the DataSource. Encountered java.sql.SQLException: Login failed for user 'sqlserver'.DSRA0010E: SQL State = S0001, Error Code = 18,456.
WebSphere Application Server indicates that there is an issue with login name sqlserver. As shown earlier, you will need to review the log files on SQL Server 2008 to find more details. Figure 10 shows the Log File viewer screen indicating error 18456.
Figure 10. Log File viewer showing error 18456
Notice that the login name of sqlserver could not be found in SQL Server 2008. In this case, the correct login name is sqlserveruser. It also gives you a State number, State 5, which means the User ID is not valid. See Table 2 for more information on States.
Not using or using an incorrect or expired password
When using a login name, you need a password. Providing an incorrect password causes this error:
[10/27/10 17:07:55:593 CDT] 00000016 DSConfigurati W DSRA8201W: DataSource Configuration: DSRA8040I: Failed to connect to the DataSource. Encountered java.sql.SQLException: Login failed for user 'sqlserveruser'.DSRA0010E: SQL State = S0001, Error Code = 18,456.
As with the previous case, you’ll need to review the log files on SQL Server to get more details on why it failed to login sqlserveruser (Figure 11).
Figure 11. Figure 11. Error 18456, State 8
Notice in the SQL Server 2008 logs, the problem reported is that the password did not match. It also gives a State number, and State 8 means that the password is incorrect (see Table 2).
Other possible causes
There are many other reasons for Error Code = 18456, as indicated by the State.
Table 2. State descriptions
|2||User ID is not valid.|
|5||User ID is not valid.|
|6||An attempt was made to use a Windows login name with SQL Server authentication.|
|7||Login is disabled, and the password is incorrect.|
|8||The password is incorrect.|
|9||Password is not valid.|
|11||Login is valid, but server access failed.|
|12||Login is valid, but server access failed.|
|13||Login tries to get access to SQL Server with services paused on it.|
|16||Login when its default database is unavailable.|
|18||Password must be changed.|
|23||Password must be changed.|
|38||Login when DEFAULT database is not available at the server.|
|40||Login when database is OFFLINE or unavailable.|
This article described some common errors that occur when connecting from WebSphere Application Server to SQL Server 2008, along with possible causes and steps to help you resolve such errors.
- Java 2 Connector authentication data entry settings
- Redbook: WebSphere Application Server V7 Administration and Configuration Guide: Accessing Databases from WebSphere
- IBM Knowledge Centers
- IBM Software Support Web site
- IBM Education Assistant
- IBM developerWorks
- IBM Redbooks
- WebSphere Software Accelerated Value Program
- IBM Support Assistant