Connecting to SQL Server 2005 via web service

Question

Delphi 2010, dbExpress, and SQL Server 2005 DB

I am trying to make a connection to a SQL Server 2005 DB using Delphi 2010 & DBExpress.

If I create a standard delphi application and hard code my connection (IT WORKS!):

procedure TForm1.Button1Click(Sender: TObject);
var
 Conn: TSQLConnection;
begin
 Conn:= TSQLConnection.Create(nil);
 Conn.ConnectionName:= 'VPUCDS_VPN_SE01';
 Conn.LoadParamsOnConnect := True;
 Conn.LoginPrompt:=True;
 try
   Conn.Connected:= True;
   if Conn.Connected then
   ShowMessage('Connected!')
   else
   ShowMessage('NOT Connected!')
 finally
  Conn.Free;
 end;
end;

All the ini files, and DLLs reside in the same folder as my executable

and yes, I have DBXMsSQL & MidasLib in the uses clause

again, it works if its not a web service!

However, if i then move the code over to a Web services CGI module:

function TTest.ConnectToDB: Boolean;stdcall;
var
 Conn: TSQLConnection;
begin
 Conn:= TSQLConnection.Create(nil);
 Conn.ConnectionName:= 'VPUCDS_VPN_SE01';  
 Conn.LoadParamsOnConnect := True;
 Conn.LoginPrompt:=True;
 try
   Conn.Connected:= True;
   result:=  Conn.Connected;
 finally
  Conn.Free;
 end;
end;

Thanks

Answer 1

I recently came across a similar situation where printing QuickReports would work fine in application form but when switched to a service did not work. This was on Windows Server 2008. Turns out the service (in your case the WebServer) needed to be installed with "NetworkService" as the user account under the logon tab. From the Windows Help:

To specify that the service uses the Network Service account, click This account, and then type NT AUTHORITY\NetworkService

To do this go Start->Run and then enter services.msc

Navigate to the IIS service and right click and select Properties and then go to Log on.

Make sure This Account is checked and it says Network Service

Answer 2

The line

Conn.LoginPrompt:=True;

is the first indication that something is wrong. A web service can not deal with a login prompt.

Second, where is VPUCDS_VPN_SE01 defined? If it is a user-specific ODBC connection, you should make it a system-wide connection.

Provide login details in the connection definition, and set LoginPrompt to false. Also, provide a way to return the cause of connection failure to the client (e.g. by passing the Exception's message).