Problem when connecting SQL through ADO

ben5000 · **Posted:** Tue May 15, 2012 9:24 am

Hi experts,

I need to connect an external SQL database from NAV so I decided to use ADO 6.0 Automation.
I've already googled for it but didn't find relevant entries...
Here is the code I use to accomplish this (example from NAV Programming Cookbook):

Code: Select all

OnRun()
CREATE(ADOConnection);

ADOConnection.ConnectionString := CreateConnectionString(
  'BTE219365', 'Demo Database NAV (6-0)', 'MANAGER', '12345678');

MESSAGE(ADOConnection.ConnectionString);

ADOConnection.Open;

CREATE(ADOCommand);
ActiveADOConnection := ADOConnection;
ADOCommand.ActiveConnection := ActiveADOConnection;

ADOCommand.CommandText := 'SELECT [Name] FROM [Company$Customer] WHERE [No_] = ''10000''';
ADOCommand.Execute ;

CREATE(ADORecordSet);
ADORecordSet.ActiveConnection := ActiveADOConnection;
ADORecordSet.Open(ADOCommand);
WHILE NOT ADORecordSet.EOF DO BEGIN
  MESSAGE(FORMAT(ADORecordSet.Fields.Item('No_').Value));
END;

ADOConnection.Close;

CreateConnectionString(ServerName : Text[50];DatabaseName : Text[50];UserName : Text[50];Password : Text[50]) : Text[1024]
EXIT ('Driver={SQL Server};' + 'Server=' + ServerName + ';' +
        'Database=' + DatabaseName + ';' + 'Uid=' + UserName +
          ' ; ' + 'Pwd=' + Password + ' ; ')

When executing the CodeUnit I get the following error message:

I've checked both parameter of the ConnectionString and everything seems to be OK.
Here is the entry I get in the Event viewer (where SQL is running):

Code: Select all

Log Name:      Application
Source:        MSSQLSERVER
Date:          15/05/2012 09:06:36
Event ID:      18456
Task Category: Logon
Level:         Information
Keywords:      Classic,Audit Failure
User:          N/A
Computer:      BTE219365.iuser.iroot.adidom.com
Description:
Login failed for user 'MANAGER '. Reason: Password did not match that for the login provided. [CLIENT: <named pipe>]
Event Xml:
<Event xmlns="http://schemas.microsoft.com/win/2004/08/events/event">
  <System>
    <Provider Name="MSSQLSERVER" />
    <EventID Qualifiers="49152">18456</EventID>
    <Level>0</Level>
    <Task>4</Task>
    <Keywords>0x90000000000000</Keywords>
    <TimeCreated SystemTime="2012-05-15T07:06:36.000000000Z" />
    <EventRecordID>19002</EventRecordID>
    <Channel>Application</Channel>
    <Computer>BTE219365.iuser.iroot.adidom.com</Computer>
    <Security />
  </System>
  <EventData>
    <Data>MANAGER </Data>
    <Data> Reason: Password did not match that for the login provided.</Data>
    <Data> [CLIENT: &lt;named pipe&gt;]</Data>
    <Binary>184800000E0000000A0000004200540045003200310039003300360035000000070000006D00610073007400650072000000</Binary>
  </EventData>
</Event>

It seems to be a problem with the password. I've tried to connect other databases on other servers and the problem remains the same.
It's quite weird since the password is correct (I can connect through SSMS with those credentials)...

Thanks for your help

mohana_cse06 · **Posted:** Tue May 15, 2012 9:44 am

Here is a example too
http://dynamicsuser.net/blogs/waldo/archive/2008/01/06/using-stored-procedures-in-c-side.aspx

There are some spaces in like

Quote:

' + Password + '

in your connection string and use some naming standards as shown in example to use parameters and all..

ben5000 · **Posted:** Tue May 15, 2012 11:22 am

Thanks for your reply,

It was indeed a space in the connection string (the one after the password parameter) that caused parsing problems. And thus was not recognized as a valid password

Thanks for your help

mohana_cse06 · **Posted:** Tue May 15, 2012 11:49 am

Welkom

mibuso.com

Problem when connecting SQL through ADO

Who is online