Board index » delphi » Cannot connect to a remote SQL Server

Cannot connect to a remote SQL Server


2007-03-15 07:44:38 PM
delphi183
Hi
I have Delphi 7.1 Pro and am trying to connect to a remote SQL Server
DB.
Using the ODBC Data Source Administrator I have created a single file
dsn (not added any User or System dsn's) I can import the SQL Server
DB into a newly created Access database by referencing this dsn as file
type ODBC Database (in Access)
I am now trying to automate this process by using a Delphi 7 and a
TADOConnection instead. I have tried 2 approaches to build the
connection string
1.In the Provider Tab I use Microsoft OLE Provider for ODBC Drivers In
the connection Tab I attempt to build another Connection string by
referencing the file dsn I have already created When I click OK on
this dsn I am prompted for user name and password After entering and
selecting OK I get following error
Connection failed
SQL State '01000'
SQL Server error 53
[Microsoft] [SQL Server Data Driver] [DBNET] Connection OPen(Connect())
2.In the Provider Tab I use Microsoft OLE Provider for SQL Server In
the server name I enter IP address, user name and password When I test
connection I get error Failed because of error in initialising provider
[DBNETLIB] etc etc
Is this problem down to me or does the SQL Manager need to do something
Many Thanks
Mike
--
 
 

Re:Cannot connect to a remote SQL Server

Mike Saunders writes:
Quote
Hi

<snip text>
check these 2 URLs. I post both because you did not specify your SQL
Server version.
www.connectionstrings.com/
www.connectionstrings.com/
--
Best regards :-)
Guillem Vicens Meier
Dep. Informática Green Service S.A.
www.clubgreenoasis.com
Contribute to the Indy Docs project: docs.indyproject.org
In order to contact me remove the -nospam
 

Re:Cannot connect to a remote SQL Server

Look for reply in .attachments under same subject
 

Re:Cannot connect to a remote SQL Server

Thanks all I seem to be able to Connect at last with the connection
Strings provided I now have a problem with the SQL to retrieve the data
I need and need to talk to the DB Manager I may be back
Mike
 

Re:Cannot connect to a remote SQL Server

I can now connect using either of the following. I set this in code as
follows
if (NOT ADOConnection1.Connected) then
try
ADOConnection1.ConnectionString :=
'Driver={SQL
Server};Server=123.456.789.012;Database=abc;Uid=def;Pwd=ghi;';
ADOConnection1.Open;
except on E:Exception do
MessageDlg('There was an error connecting to the database.
Error:' +
#13#10 + e.Message,
mtError, [mbOk],0);
end;
Note that I can also use Driver=SQLOLEDB instead
The problem I now have is accessing the data. I have been given Read
Only Access to the data I want so when I execute a TADOQuery I get
Select Permission Denied on Object I can see in the wizard for building
the connection string that access permissions are in Tab3 but when I
try to Test the connection this way using {SQL Server} in connection
string I get the following This puzzles me as it works OK when executed
from code
Test Connection Failed because of error in Initailising provider
[Microsoft][ODBC SQL Server Driver] [DBNETLIB] Connecion Open
(Connect())
I would prefer to all I need in code How do I overcome this Read Only
problem
Many thanks
Mike
 

Re:Cannot connect to a remote SQL Server

Quote
Access permissions in the Connection string are not relevant for SQL
server. SQL server grants permissions on each object including each
table in the database. The error you are getting indicates the login
you are using does not have permission to read at least one table in
your query.
Got it working at last (I was selecting from the wrong table) I have
one more question however
Whenever I change the SQL string in the ADO query even though I am not
connected it appears something is executing as for around 20 seconds or
so I cannot proceed
Do you know what is happening (I could put it all in code of course)
Thasks
Mike