Microsoft KB Archive/304310

Knowledge Base

BUG: SQLOLEDB Provider Ignores or Removes Trailing Spaces from Password on the Connection Open Method

Article ID: 304310

Article Last Modified on 11/5/2003

---

APPLIES TO

• Microsoft OLE DB Provider for SQL Server 7.0
• Microsoft OLE DB Provider for SQL Server 7.01

---

This article was previously published under Q304310

SYMPTOMS

If a Microsoft SQL Server password includes trailing spaces, the OLE DB Provider for SQL Server (SQLOLEDB) ignores or removes those spaces before it passes the credentials to the backend server. In addition, you receive the following error message:

Run-time error '-2147217843(80040e4d)':
Login failed for user 'UserName'.

This problem does not occur with OLE DB Provider for ODBC Drivers.

RESOLUTION

To work around this problem, use the OLE DB Provider for ODBC Driver instead of the OLE DB Provider for SQL Server, or do not create passwords with trailing spaces.

STATUS

Microsoft has confirmed that this is a bug in the Microsoft products that are listed at the beginning of this article.

MORE INFORMATION

Steps to Reproduce Behavior

1. Open a new Standard EXE project in Microsoft Visual Basic 6.0. Form1 is created by default.
2. Set a reference to Microsoft ActiveX Data Objects 2.X.

3. Copy and paste the following code into the code window of Form1:
   
   Note You must change User ID=<UID> and password= to the correct values before you run this code. Make sure that <UID> has the appropriate permissions to perform this operation on the database.

   Private Sub Form_Load()
   
   Dim cn As New Connection
   Dim cn2 As New Connection
   Dim cn3 As New Connection
   
       cn.Open "Provider=sqloledb;Data source=scout333;" & _
        "User ID=<UID>;Password=<strong password>;initial catalog=northwind"
   On Error Resume Next
       cn.Execute "sp_revokedbaccess 'User1'"
       cn.Execute "sp_droplogin 'User1'"
       
   On Error GoTo 0
       cn.Execute "sp_addlogin 'User1', 'Test   ', 'Northwind'"
       cn.Execute "sp_adduser 'User1', 'User1'"
   
       cn2.Open "Provider=MSDASQL;Driver=sql server;" & _
        "server=scout333;UID=User1;PWD=Test   ;"
       MsgBox "Connection State: " & cn2.State
       cn3.Open "Provider=sqloledb;Data source=scout333;" & _
        "User ID=User1;Password=Test   ;"
   
   End Sub
                       

4. Be sure to change the connection string for the cn object so it correctly uses an existing SQL Server log on. Also, change the connection strings for the cn2 and cn3 objects so they correctly point to your SQL Server.
5. Run the code. When the connection that uses the OLE DB Provider for ODBC Drivers connects, you receive confirmation of this connection in a message box. This message box contains a connection state of 1 (adStateOpen) if the connection is made properly.
6. Click OK in the message box. The code attempts to make the connection by using the OLE DB Provider for SQL Server. This fails with the above-mentioned error message.

REFERENCES

For additional information about opening ADO connections, click the following article number to view the article in the Microsoft Knowledge Base:

168336 HOWTO: Open ADO Connection and Recordset Objects

Keywords: kbbug kbnofix kbprovider KB304310

---

Send feedback to Microsoft

© Microsoft Corporation. All rights reserved.