Board index » delphi » (DB Rookie) Connecting to SQL Server 2000 using ADO

(DB Rookie) Connecting to SQL Server 2000 using ADO

What do i have to do to successfully use ADO components to access a database
on a SQL Server. I am using Delphi 7 Architect trial currently? I tried to
setup a Data Link file but no matter what i cannot successfully get logged
into the damn thing.

From the ConnectionString property in the OI, i then click Build which leads
me to the Data Link Properties dialog. From here i select "Microsoft OLE DB
Provider for SQL Server" on the first page of the dialog. The second page,
the topmost dropdown, is always empty. I can type in the name of my server
but cannot select it from the dropdown. Truthfully, the dropdown is
completely empty. Then the next area is the log in information. I choose to
use Windows authentication. Then for number 3 i always get an error telling
me my login fails. I have tried over and over and over again with all kinds
of varieties of UN/Password combinations. And yes they were all correct and
tried multiple times. The accounts are not locked out either. I cannot get
beyond this point. Can anyone please help me connect my Delphi app to this
SQL Server using the ADO componentry?

TYIA,

Brian Hollister

 

Re:(DB Rookie) Connecting to SQL Server 2000 using ADO


Hi Brian

Login fails would suggest that you can connect to the sql server but are
being rejected.

Is this the desktop edition or the full edition ?
What O/S is SQL running on.
Have you tried sa for the username and SQL Authentication ?
Are you running in a domain ?
Have you tried making a connection from Control Panel ODBC connections?

--

Brian Slack
http://www.depicus.com
"Wake On Lan" and "Remote Shutdown" Software

Quote
"Brian" <bhollisterATfuturaintlDOTcom> wrote in message

news:3e381055$1@newsgroups.borland.com...
Quote
> What do i have to do to successfully use ADO components to access a
database
> on a SQL Server. I am using Delphi 7 Architect trial currently? I tried to
> setup a Data Link file but no matter what i cannot successfully get logged
> into the damn thing.

> From the ConnectionString property in the OI, i then click Build which
leads
> me to the Data Link Properties dialog. From here i select "Microsoft OLE
DB
> Provider for SQL Server" on the first page of the dialog. The second page,
> the topmost dropdown, is always empty. I can type in the name of my server
> but cannot select it from the dropdown. Truthfully, the dropdown is
> completely empty. Then the next area is the log in information. I choose
to
> use Windows authentication. Then for number 3 i always get an error
telling
> me my login fails. I have tried over and over and over again with all
kinds
> of varieties of UN/Password combinations. And yes they were all correct
and
> tried multiple times. The accounts are not locked out either. I cannot get
> beyond this point. Can anyone please help me connect my Delphi app to this
> SQL Server using the ADO componentry?

> TYIA,

> Brian Hollister

Re:(DB Rookie) Connecting to SQL Server 2000 using ADO


Thank you Brian(sounds like i am speaking to myself, whoa),

Edition := SQL Server 2000 Enterprise Edition Evaluation Copy
O/S SQL runs on := Windows 2000 Terminal
sa attempt := FALSE but will try now though just for kicks but I will still
need to know why my login would not work
Running in a domain := TRUE, The Terminal server running SQL runs in one
domain and the development machine is in another domain.
Creating connection through C/P applets := TRUE/FALSE, i succeeded in
creating an ODBC data source and connecting using the traditional database
components included with Delphi 5 Professional to an Access database. Using
ADO to connect i would think that i would not need to do this at all. Isn't
that dialog for ODBC connectivity?

This application is for internal use for our tech support team. It will
track call tickets, keep and maintain a knowledgebase, and a multitude of
other tasks. So as you can tell we need the power of SQL Server or the like.
This is what pushes the desire to try out SQL and the ADO components.

Quote
"Brian Slack" <newsgro...@depicus.com> wrote in message

news:3e381269@newsgroups.borland.com...
Quote
> Hi Brian

> Login fails would suggest that you can connect to the sql server but are
> being rejected.

> Is this the desktop edition or the full edition ?
> What O/S is SQL running on.
> Have you tried sa for the username and SQL Authentication ?
> Are you running in a domain ?
> Have you tried making a connection from Control Panel ODBC connections?

> --

> Brian Slack
> http://www.depicus.com
> "Wake On Lan" and "Remote Shutdown" Software

> "Brian" <bhollisterATfuturaintlDOTcom> wrote in message
> news:3e381055$1@newsgroups.borland.com...
> > What do i have to do to successfully use ADO components to access a
> database
> > on a SQL Server. I am using Delphi 7 Architect trial currently? I tried
to
> > setup a Data Link file but no matter what i cannot successfully get
logged
> > into the damn thing.

> > From the ConnectionString property in the OI, i then click Build which
> leads
> > me to the Data Link Properties dialog. From here i select "Microsoft OLE
> DB
> > Provider for SQL Server" on the first page of the dialog. The second
page,
> > the topmost dropdown, is always empty. I can type in the name of my
server
> > but cannot select it from the dropdown. Truthfully, the dropdown is
> > completely empty. Then the next area is the log in information. I choose
> to
> > use Windows authentication. Then for number 3 i always get an error
> telling
> > me my login fails. I have tried over and over and over again with all
> kinds
> > of varieties of UN/Password combinations. And yes they were all correct
> and
> > tried multiple times. The accounts are not locked out either. I cannot
get
> > beyond this point. Can anyone please help me connect my Delphi app to
this
> > SQL Server using the ADO componentry?

> > TYIA,

> > Brian Hollister

Re:(DB Rookie) Connecting to SQL Server 2000 using ADO


While awaiting the reply,

I added the ODBC connection on the development machine and voila, it
connects using Windows Authentication. I did notice though that with this
method i do not need to enter a username or password, nor does it matter
what i put in there. The database will connect either way. However, if i
build the ConnectionString to include use a specific name i cannot get in no
matter what UN/Password combo. For now though i am happy to be connected.
Now to see if i can actually access the tables. For now though why did
creating an ODBC data source allow me to connect with ADO componentry? The
use of both seems confusing to me. Thanks...

Quote
"Brian Slack" <newsgro...@depicus.com> wrote in message

news:3e3827b6@newsgroups.borland.com...
Quote
> Hi Brian

> Feels like answering myself :)

> Multi domain may be your problem

> Windows authentication only adds admins from the local domain by default

> Go to the SQL server and add yourself as a user under Security > Logins

> The ODBC uses the same method to connect to SQL so it is a good test

> --

> Brian Slack
> http://www.depicus.com
> "Wake On Lan" and "Remote Shutdown" Software

> "Brian" <bhollisterATfuturaintlDOTcom> wrote in message
> news:3e382010@newsgroups.borland.com...
> > Thank you Brian(sounds like i am speaking to myself, whoa),

> > Edition := SQL Server 2000 Enterprise Edition Evaluation Copy
> > O/S SQL runs on := Windows 2000 Terminal
> > sa attempt := FALSE but will try now though just for kicks but I will
> still
> > need to know why my login would not work
> > Running in a domain := TRUE, The Terminal server running SQL runs in one
> > domain and the development machine is in another domain.
> > Creating connection through C/P applets := TRUE/FALSE, i succeeded in
> > creating an ODBC data source and connecting using the traditional
database
> > components included with Delphi 5 Professional to an Access database.
> Using
> > ADO to connect i would think that i would not need to do this at all.
> Isn't
> > that dialog for ODBC connectivity?

> > This application is for internal use for our tech support team. It will
> > track call tickets, keep and maintain a knowledgebase, and a multitude
of
> > other tasks. So as you can tell we need the power of SQL Server or the
> like.
> > This is what pushes the desire to try out SQL and the ADO components.

> > "Brian Slack" <newsgro...@depicus.com> wrote in message
> > news:3e381269@newsgroups.borland.com...
> > > Hi Brian

> > > Login fails would suggest that you can connect to the sql server but
are
> > > being rejected.

> > > Is this the desktop edition or the full edition ?
> > > What O/S is SQL running on.
> > > Have you tried sa for the username and SQL Authentication ?
> > > Are you running in a domain ?
> > > Have you tried making a connection from Control Panel ODBC
connections?

> > > --

> > > Brian Slack
> > > http://www.depicus.com
> > > "Wake On Lan" and "Remote Shutdown" Software

> > > "Brian" <bhollisterATfuturaintlDOTcom> wrote in message
> > > news:3e381055$1@newsgroups.borland.com...
> > > > What do i have to do to successfully use ADO components to access a
> > > database
> > > > on a SQL Server. I am using Delphi 7 Architect trial currently? I
> tried
> > to
> > > > setup a Data Link file but no matter what i cannot successfully get
> > logged
> > > > into the damn thing.

> > > > From the ConnectionString property in the OI, i then click Build
which
> > > leads
> > > > me to the Data Link Properties dialog. From here i select "Microsoft
> OLE
> > > DB
> > > > Provider for SQL Server" on the first page of the dialog. The second
> > page,
> > > > the topmost dropdown, is always empty. I can type in the name of my
> > server
> > > > but cannot select it from the dropdown. Truthfully, the dropdown is
> > > > completely empty. Then the next area is the log in information. I
> choose
> > > to
> > > > use Windows authentication. Then for number 3 i always get an error
> > > telling
> > > > me my login fails. I have tried over and over and over again with
all
> > > kinds
> > > > of varieties of UN/Password combinations. And yes they were all
> correct
> > > and
> > > > tried multiple times. The accounts are not locked out either. I
cannot
> > get
> > > > beyond this point. Can anyone please help me connect my Delphi app
to
> > this
> > > > SQL Server using the ADO componentry?

> > > > TYIA,

> > > > Brian Hollister

Re:(DB Rookie) Connecting to SQL Server 2000 using ADO


This is how I do my connection strings

//if windows auth then
adoLogin.ConnectionString:='Provider=SQLOLEDB.1;Integrated
Security=SSPI;Persist Security Info=False;Initial Catalog=Agency
Manager;Data Source=' + edtServer.Text
//if SQL auth
adoLogin.ConnectionString:='Provider=SQLOLEDB.1;Password=' +
edtPassword.Text + ';Persist Security Info=True;User ID=' + edtUserName.Text
+ ';Initial Catalog=Agency Manager;Data Source=' + edtServer.Text;

While I am sure there are people that can tell you the merits or otherwise
of ADO -v- ODBC they both use the same connection methods.

Usually SQL has two methods to connect (Named Pipes and TCP/IP)

From what I can gather the named pipes approach simply attempts to open the
pipe and if it works then ok.

TCP/IP uses a UDP broadcast to find the names of servers but with ADO this
sometimes fails. It seems to be better with 2.7 but ODBC appears to be the
most solid.

I have noticed that un service packed machines tend to struggle some times
and an ODBC kick seems to help especially if connecting to SQL servers over
the internet. Don't know why I just put it down to M$

--

Brian Slack
http://www.depicus.com
"Wake On Lan" and "Remote Shutdown" Software

Quote
"Brian" <bhollisterATfuturaintlDOTcom> wrote in message

news:3e382b2b$1@newsgroups.borland.com...
Quote
> While awaiting the reply,

> I added the ODBC connection on the development machine and voila, it
> connects using Windows Authentication. I did notice though that with this
> method i do not need to enter a username or password, nor does it matter
> what i put in there. The database will connect either way. However, if i
> build the ConnectionString to include use a specific name i cannot get in
no
> matter what UN/Password combo. For now though i am happy to be connected.
> Now to see if i can actually access the tables. For now though why did
> creating an ODBC data source allow me to connect with ADO componentry? The
> use of both seems confusing to me. Thanks..

Other Threads