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
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.
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.
- Early Bird discount: Register before 18 February
- 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...
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 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).
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. 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.
- 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
- Forums and newsgroups
- Java technology Forums
- WebSphere Support Technical Exchange on Facebook
- Global WebSphere Community on WebSphere.org
- Follow IBM Support on Twitter!
Dig deeper into WebSphere on developerWorks
Get samples, articles, product docs, and community resources to help build, deploy, and manage your cloud apps.
Keep up with the best and latest technical info to help you tackle your development challenges.
Software development in the cloud. Register today to create a project.
Evaluate IBM software and solutions, and transform challenges into opportunities.