Microsoft KB Archive/304310

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



Steps to Reproduce Behavior
 Open a new Standard EXE project in Microsoft Visual Basic 6.0. Form1 is created by default. Set a reference to Microsoft ActiveX Data Objects 2.X.  Copy and paste the following code into the code window of Form1:

Note You must change User ID= and password= to the correct values before you run this code. Make sure that  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 &quot;Provider=sqloledb;Data source=scout333;&quot; & _ &quot;User ID=;Password= ;initial catalog=northwind&quot; On Error Resume Next cn.Execute &quot;sp_revokedbaccess 'User1'&quot; cn.Execute &quot;sp_droplogin 'User1'&quot; On Error GoTo 0 cn.Execute &quot;sp_addlogin 'User1', 'Test  ', 'Northwind'&quot; cn.Execute &quot;sp_adduser 'User1', 'User1'&quot;

cn2.Open &quot;Provider=MSDASQL;Driver=sql server;&quot; & _ &quot;server=scout333;UID=User1;PWD=Test  ;&quot; MsgBox &quot;Connection State: &quot; & cn2.State cn3.Open &quot;Provider=sqloledb;Data source=scout333;&quot; & _ &quot;User ID=User1;Password=Test  ;&quot;

End Sub  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.</li> 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.</li> 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.</li></ol>

<div class="references_section">