The Support Authority: Resolving connection errors between WebSphere Application Server and SQL Server

SQL Server® is one of the more popular databases that is used with IBM® WebSphere® Application Server, as well as with IBM WebSphere Portal and IBM WebSphere Commerce. This article will help you resolve some common errors that can occur when connecting from WebSphere Application Server to SQL Server 2008. This content is part of the IBM WebSphere Developer Technical Journal.

David Kenner (david_kenner@us.ibm.com), WebSphere Application Server L2 Support , IBM

David Kenner is a WebSphere Application Server L2 Support representative. He has been working in WebSphere Application Server, Portal, Commerce and AIX for 10 years, both in IBM and in one of the world’s largest retailer. He currently works in the WASCET team which handles database issues with Application Server and works daily with DB2 Support. He has certifications in both Application Server and DB2.



Dr. Mahesh Rathi (mrathi@us.ibm.com), WebSphere Application Server SWAT Team, IBM

Dr. Mahesh Rathi has been involved with WebSphere Application Server product since its inception. He led the security development team before joining the L2 Support team, and joined the SWAT team in 2005. He thoroughly enjoys working with demanding customers, on hot issues, and thrives in pressure situations. He received his PhD in Computer Sciences from Purdue University and taught Software Engineering at Wichita State University before joining IBM.



10 November 2010

Also available in Chinese Russian

In each column, The Support Authority discusses resources, tools, and other elements of IBM® Technical Support that are available for WebSphere® products, plus techniques and new ideas that can further enhance your IBM support experience.

This just in...

As always, we begin with some new items of interest for the WebSphere community at large:

Continue to monitor the various support-related Web sites, as well as this column, for news about other tools as we encounter them.

And now, on to our main topic...


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 and IBM WebSphere Commerce. 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 nameSQL Server 2008WebSphere Application Server
No login name, no passwordNot allowedAllowed
Login name, no passwordAllowedNot allowed
Login name with passwordAllowedAllowed

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
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
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:

Listing 1
[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
Figure 3. SQL Server logs

Now, using the timestamp from the WebSphere Application Server SystemOut.log file, such as 9/8/10 17:37:35:953 CDT, you can search through SQL Server 2008 logs and find the corresponding log file entry (Figure 4).

Figure 4. Log File Viewer
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
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
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
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
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
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:

Listing 2
[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:

Listing 3
[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
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:

Listing 4
[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. Error 18456, State 8
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
StateDescription
2User ID is not valid.
5User ID is not valid.
6An attempt was made to use a Windows login name with SQL Server authentication.
7Login is disabled, and the password is incorrect.
8The password is incorrect.
9Password is not valid.
11Login is valid, but server access failed.
12Login is valid, but server access failed.
13Login tries to get access to SQL Server with services paused on it.
16Login when its default database is unavailable.
18Password must be changed.
23Password must be changed.
38Login when DEFAULT database is not available at the server.
40Login when database is OFFLINE or unavailable.

Conclusion

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.

Resources

Learn

Get products and technologies

Discuss

Comments

developerWorks: Sign in

Required fields are indicated with an asterisk (*).


Need an IBM ID?
Forgot your IBM ID?


Forgot your password?
Change your password

By clicking Submit, you agree to the developerWorks terms of use.

 


The first time you sign into developerWorks, a profile is created for you. Information in your profile (your name, country/region, and company name) is displayed to the public and will accompany any content you post, unless you opt to hide your company name. You may update your IBM account at any time.

All information submitted is secure.

Choose your display name



The first time you sign in to developerWorks, a profile is created for you, so you need to choose a display name. Your display name accompanies the content you post on developerWorks.

Please choose a display name between 3-31 characters. Your display name must be unique in the developerWorks community and should not be your email address for privacy reasons.

Required fields are indicated with an asterisk (*).

(Must be between 3 – 31 characters.)

By clicking Submit, you agree to the developerWorks terms of use.

 


All information submitted is secure.

Dig deeper into WebSphere on developerWorks


static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=1
Zone=WebSphere
ArticleID=577220
ArticleTitle=The Support Authority: Resolving connection errors between WebSphere Application Server and SQL Server
publish-date=11102010