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:

• It seems like we just got done with Impact 2010 and it is time to start planning for Impact 2011.
  • Early Bird discount: Register before 18 February
    Join us for Impact 2011: April 10-15, 2011 in Las Vegas Nevada, at The Venetian and The Palazzo Hotels.
  • Call for Speakers: Open now through 7 January
    Interested in hosting a session at Impact 2011? Submit your proposal online for consideration before January 7, 2011.
  • Sponsorship opportunities
    Check out the exciting Impact 2011 Sponsorships and Marketing Promotional Opportunities to help you get the most out of your conference experience.
  • Top 5 reasons to attend Impact 2011 (PDF, 115KB)
    Experience the one conference where Business and IT leaders can explore together how to achieve greater business agility.
• Earlier this year, the IBM Support Portal was named one of the Top Ten Support Sites of 2010 by the Association of Support Professionals. Have you tried the IBM Support Portal yet? All IBM software products are now included, and all software product support pages have been replaced by IBM Support Portal. See the Support Authority's Introduction to the new IBM Support Portal for details.
• Learn, share, and network at the IBM Electronic Support Community blog on developerWorks.
• Check out the new Global WebSphere Community at websphereusergroup.org. Customize the content on your personalized GWC page and connect to other "WebSpherians" with the same interests.
• Several exciting webcasts are planned in through October at the WebSphere Technical Exchange. Check the site for details and become a fan on Facebook!

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

Back to top

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.

Back to top

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.

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.

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.

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.

Back to top

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.

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).

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).

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.

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).

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.

Back to top

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).

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.

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.

Back to top

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).

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).

Back to top

Other possible causes

There are many other reasons for Error Code = 18456, as indicated by the State.

Back to top

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

• Information Center: Java 2 Connector authentication data entry settings
  
  
• Redbook: WebSphere Application Server V7 Administration and Configuration Guide: Accessing Databases from WebSphere
  
  
• The Support Authority: If you need help with WebSphere products, there are many ways to get it
  
  
• IBM Software product Information Centers
  
  
• IBM Software Support Web site
  
  
• IBM Education Assistant
  
  
• IBM developerWorks
  
  
• IBM Redbooks
  
  
• WebSphere Software Accelerated Value Program
  
  

Get products and technologies

• IBM Software Support Toolbar
  
  
• IBM Support Assistant
  
  

Discuss

• Forums and newsgroups
  
  
• Java technology Forums
  
  
• WebSphere Support Technical Exchange on Facebook
  
  
• Global WebSphere Community on WebSphere.org
  
  
• Follow IBM Support on Twitter!
  • WebSphere Electronic Support
  • WebSphere Application Server information
  • WebSphere Process Server
  • WebSphere MQ
  • WebSphere Business Process Management
  • WebSphere Business Modeler
  • WebSphere Adapters
  • WebSphere DataPower Appliances
  • WebSphere Commerce
  • IBM Support Assistant Tools
  
  

About the authors

Rate this article

Comments

Back to top

Table of contents

• This just in...
• Achieving a clean connection
• WebSphere Application Server and SQL Server together
• Not using SQL Server authentication
• Not using or using an incorrect login name
• Not using or using an incorrect or expired password
• Other possible causes
• Conclusion
• Resources
• About the authors
• Comments

Next steps from IBM

• Try: WebSphere Application Server
• Briefing: Building SOA solutions and managing the service lifecycle
• Buy: WebSphere Application Server

Dig deeper into WebSphere on developerWorks

Try IBM PureSystems. No charge.

Experience today!

---

Get started with the cloud-based trial and pattern development kit.

Special offers