Using ADO to connect to SQL Server 2005 or 2008 (Express and standard) using Windows Authentication

Hi,

I apologize in advance if this is an overly simple question or I have simply 
overlooked the obvious.

With the current project (it's client server not n-tier) I'm on, we usually 
connect using SQL Server authentication.   We now have a customer that would 
like to us to connect to SQL server using Windows Authentication.  The 
customer would like to control all access to SQL server using Active 
Directory rather than us having the username and password on the client side 
connecting via SQL Server Authentication.  The app is still in D5 (don't ask 
;) ), with all the service packs.

I'm looking at the ADOConnection component have had success connecting, but 
don't know how to pass the database in that I want to connect to.  The 
"DefaultDatabase" isn't supported using the provider I'm trying out.  So to 
the point:

1. What would be the best provider to use (name)?
2. Any one got a sample project and/or instructions and/or best practices on 
steps to get connected and run queries?

Thank you!
Curt
0
Curt
5/12/2010 8:41:20 PM
embarcadero.delphi.ado 597 articles. 1 followers. Follow

6 Replies
1078 Views

Similar Articles

[PageSpeed] 46

> {quote:title=Curt Krueger wrote:}{quote}
> Hi,
> 
> I apologize in advance if this is an overly simple question or I have simply 
> overlooked the obvious.
> 
> With the current project (it's client server not n-tier) I'm on, we usually 
> connect using SQL Server authentication.   We now have a customer that would 
> like to us to connect to SQL server using Windows Authentication.  The 
> customer would like to control all access to SQL server using Active 
> Directory rather than us having the username and password on the client side 
> connecting via SQL Server Authentication.  The app is still in D5 (don't ask 
> ;) ), with all the service packs.

Using the ADOConnection, it's pretty easy. (I don't have D5 any longer, though - tested with D7 and D2010).

Click on the ADOConnection.ConnectionString property in the Object Inspector, then click on the button with the ellipsis that appears.

In the resulting dialog, click the 'Build' button.

In the next dialog, choose your provider (for mine, with SQL Server Express 2005, it's "Microsoft OLE DB Provider for SQL Server" I chose for simplicity). Click "Next".

Choose (or just enter) the name for your server, including the computer name (eg., "YourServerPC\SQLEXPRESS"). Check the box for "Windows NT Authentication", and choose a database from the drop down list (the list may take a little while to populate).

Once you've chosen the database, click the "Test Connection" button. If everything is configured correctly, you should get a message that the test was successful. Click "OK" to close the dialog, and you'll be back at the dialog where you clicked the "Build" button. You'll see the entire connection string in the edit control, or you can click OK on that dialog as well and see the connection string in the Object Inspector.

That string will give you info on the provider name, what to use to specify Windows Authentication, and how to enter the name of the database and so forth.
0
Ken
5/13/2010 2:08:36 PM
See www.connectionstrings.com for detailed info.

But the following should work for you.

ConnectionString=Provider=SQLOLEDB.1;Data Source=CLIENTS_SERVER_NAME;Initial 
Catalog=CLIENTS_DB_NAME;Integrated Security=SSPI;

Top Tip: on Clients server, create a new text file on desktop, rename it 
with .UDL ext. Then double click it to launch DataLinkProperties editor and 
setup and test a connection.

HTH.

Rob.



"Curt Krueger" <curt_krueger@nospamyahoo.com> wrote in message 
news:241996@forums.embarcadero.com...
> Hi,
>
> I apologize in advance if this is an overly simple question or I have 
> simply overlooked the obvious.
>
> With the current project (it's client server not n-tier) I'm on, we 
> usually connect using SQL Server authentication.   We now have a customer 
> that would like to us to connect to SQL server using Windows 
> Authentication.  The customer would like to control all access to SQL 
> server using Active Directory rather than us having the username and 
> password on the client side connecting via SQL Server Authentication.  The 
> app is still in D5 (don't ask ;) ), with all the service packs.
>
> I'm looking at the ADOConnection component have had success connecting, 
> but don't know how to pass the database in that I want to connect to.  The 
> "DefaultDatabase" isn't supported using the provider I'm trying out.  So 
> to the point:
>
> 1. What would be the best provider to use (name)?
> 2. Any one got a sample project and/or instructions and/or best practices 
> on steps to get connected and run queries?
>
> Thank you!
> Curt
0
Robert
5/13/2010 3:55:59 PM
Thank you guys very much!
Curt
0
Curt
5/13/2010 4:12:33 PM
> {quote:title=Robert Priestley wrote:}{quote}
> Top Tip: on Clients server, create a new text file on desktop, rename it 
> with .UDL ext. Then double click it to launch DataLinkProperties editor and 
> setup and test a connection.

Nice! I'd forgotten this; I'll have to save it in my notes file for future reference.
0
Ken
5/13/2010 4:46:17 PM
"Robert Priestley" <Robert.PriestleyNo@SpamCodegate.co.uk> wrote in message 
news:242273@forums.embarcadero.com...
> See www.connectionstrings.com for detailed info.
> Top Tip: on Clients server, create a new text file on desktop, rename it 
> with .UDL ext. Then double click it to launch DataLinkProperties editor 
> and setup and test a connection.
>

Anyone know if there's something similar to help determine an ADO.Net 
connection string?

Thanks,
Ray
0
Lester
5/13/2010 8:26:33 PM
> {quote:title=Curt Krueger wrote:}{quote}
> Hi,
> 
> I apologize in advance if this is an overly simple question or I have simply 
> overlooked the obvious.
> 
> With the current project (it's client server not n-tier) I'm on, we usually 
> connect using SQL Server authentication.   We now have a customer that would 
> like to us to connect to SQL server using Windows Authentication.  The 
> customer would like to control all access to SQL server using Active 
> Directory rather than us having the username and password on the client side 
> connecting via SQL Server Authentication.  The app is still in D5 (don't ask 
> ;) ), with all the service packs.
> 
> I'm looking at the ADOConnection component have had success connecting, but 
> don't know how to pass the database in that I want to connect to.  The 
> "DefaultDatabase" isn't supported using the provider I'm trying out.  So to 
> the point:
> 
> 1. What would be the best provider to use (name)?
> 2. Any one got a sample project and/or instructions and/or best practices on 
> steps to get connected and run queries?
> 
You can set the database to use in your code before you open the TadoConnection using the TadoConnection.properties

ADOConnection1.Properties.Item['initial Catalog'].Value := 'YourDatabaseName';
0
Brian
5/14/2010 2:19:10 AM
Reply:

Similar Artilces:

Using forms authentication
HiI am writing an internet application that requires forms authentication to login remote usersI also want to connect to sql server using windows authenticationThe question is can Ido this?I thought if I allow IIS anonymous access, create an actual windows account to use for the anonymous access then in web.config, set impersonate = true and authentication mode = formsWill this work Many thanks Windows authentication is not delegatable unless you set up your configuration to use Kerberos.Setting up the anonymous account as a domain account with access to the database and impersonating this...

Using forms authentication but connect to sql server using windows authentication
HiI want to use forms authentication to login to my internet application, but I want to connect to sql server 2000 using windows authentication ( via impersonation of a single windows domain account ) ( Windows 2000 server )Both web server and sql server are running on different machinesCan you tell me the configurations I need to do to allow thisThanks timoth by default the ASP.Net will try to connect with your SQl Server using ASP.Net Services Account. If you are not using a Domain Account for ASp.Net service then you can use Mirrored account i.e. create a local account with same name an...

Visual Studio 2008 SP1
I have the developer version of SQL 2005 & SQL Server Express 2008 installed on my laptop and Visual Studio 2008 SP1 and .Net Framework 3.5 SP1 installed.    I'm learning ASP.Net MVC and want to work with a SQL Server Express 2008, but I can't connect to SQL Server Express 2008.  I first tried to ADD a SQL Server Database by adding a SQL Server Database to app_data and received the error "Connections to SQL Server files (*.mdf) require SQL Server Express 2005 to function properly.  Then I attempted to ADD the Membership.MDF ...

SQL Server 2005 connection using Windows Authentication
is there a way to connect from PB 10.5.1 to SQL Server 2005 using Windows Authentication? On security tab, integrated field add "SSPI" in the connection dialog. Leave user name and password blank. <egattu@yahoo.com> wrote in message news:468ebb49$1@forums-1-dub... > is there a way to connect from PB 10.5.1 to SQL Server 2005 using Windows > Authentication? ...

How to connect to Remote SQL Server database using SQL Server 2005 Express
Hi,I've an account with a hosting service provider online for SQL Server database. I've downloaded SQL Server 2005 Express from ASP.Net. How can I use it to connect to my SQL Server Database which is sitting on remote server? The hosting provider gave me following things to connect to the remote database.Server NameDatabase NameUser NamePasswordRegards,A.K.R    Anyone? I don't want to ask the obvious, but is there not a Read Me file included with the download of SQL Express which would explain how to connect? Sorry I can't be more helpful, but I've only ever briefly ...

Use Custom Service Account for connecting to SQL Server using windows authentication
My web server has IIS 6.0 and is not in my company's domain. I created a virtual drectory for the ASP. NET 2005 application. I am trying to connect to SQL server which is in domain with Windows Authentication. I created a local account on WEB SERVER ex. CustomASP, I assigned it with ASP.NET permissions. Created a new application pool with configurable user the local account and assigned the ASP .NET application to the pool. Now I need to create on SQL Server a login & grant access to the custom service account created (handles the application pool). How am i goin...

If I use javascript in my pages, can I still use ADO to connect to SQL server?
I'm not exactly sure what I'm doing here.  (I'm sure it shows)  I want to start using javascript and ajax in my ASP.net pages, but am currently using ADO (I think) to communicate with my SQL server.  Here's a sample script..   Dim objConn As New SqlConnection(ConfigurationSettings.AppSettings("DSN")) Dim objCmd As SqlCommand Dim objRdr As SqlDataReader  Sub LoadMenubar()  Try   objConn.Open()   objCmd = New SqlCommand("SELECT * FROM Menubar ORDER BY Priority", objConn) &...

Cannot connect to local SQL Server using Vista using Windows Authentication. Please help.
I have made a fresh "default" install of Visual Studio 2008 on a fresh install of Vista 32bit. SQL Server 2005 Express has been installed as part of that process. I can access it using SQL Server Management Studio Express but from within Visual Studio I cannot. If I drag an SQL Data Source control onto a page and configure it to connect to the local server (using Windows Authentication) I get.... Cannot Connect.SQL Server does not allow remote connections.Provider: Named pipes provider, error 40 could not open a connection to SQL Server. Please help I am getting desperate!VWGo...

How to get and use token from Identity using Windows Authentication to connect to SQL 2005 from ASP.NET 2.0
The web server and SQL server are on 2 different servers in the same domain.  It may be the same server when the files get pushed to production.  I have set up IIS to use Integrated Windows Authentication only.  In my web.config file I have the following settings.. <connectionStrings>    <add name="ConnectionStringName" connectionString="Data Source=Servername;Initial Catalog=Databasename;Integrated Security=SSPI"      providerName="System.Data.SqlClient" />  </connectionStrings> <authenticat...

This version of Microsoft Sql Server Management Studio can only be used to connect to Sql Server 2000 and Sql Server 2005 servers
When I connect SQL Server 2005 to a remote database i get the error: "This version of Microsoft Sql Server Management Studio can only be used to connect to Sql Server 2000 and Sql Server 2005 servers". Can I confirm it is because: the remote db is of SQL Server 2008? There is a soln mentioned in http://forums.asp.net/t/1324047.aspx Did anybody try this (installing 2008 express: http://www.microsoft.com/express/sql/download/)? I am just checking again, whether it will disturb my existing SQL Server installation, in any means...? Though it is mentioned that it will g...

Error using Visual Studio 2008 SP1 ASP.NET 3.5 to connect to SQL Server Express 2005 AdventureWorks.
I am following the lerning ASP.NET 3.5 by O'REILLY to write some ASP.NET 3.5 code using Visual Studio 2008 SP1. I have gotten to the point to where I need to Configure a Data Source using the SqlDataSource control. I chose Microsoft SQL Server (SqlClient) even though I have SQL Server Express 2005 - This seems like the only logical choice. I get to the Add Connection window and successfully add a Server name and I use the Select or enter a database name of AdventureWorks. When I click on Test Connection I get the Test Connection Succeeded message box. Unfortunately, when I click OK I ...

Connecting SQL SERVER 2005 EXPRESS EDITION using Server Explorer
Hi all.I use Visual Studio .NET 2002 and SQL Server 2005 Express Edition as database.I try to connect to database using server explorer.Test connection is OK. When I done with it and click OK, the message prompt that "Unable to connect to database".So, is it impossible to connect SQL Server 2005 Express Edition using the server explorer?But, the connection is ok when I connect through code cause I can retrieve and update database.  Hi azrina, From your description I would guess, are you connecting to your sql express database both from your code and server explorer? and bes...

Windows Server 2008 / SQL Express 2005 / Visual Studio 2008 / Authentication / ASP.NET 2.0.50727
Hello, Two weeks ago I installed Windows Server 2008 standard on my home computer, Installed all updates service packs, etc.  I then installed Visual Studio 2008 and installed all updates and service packs, etc. and began to write a website.  I'm using C# with forms authentication.  I am using standard login, loginview controls provided with Visual Studio 2008. When I run run the application from http://localhost I am able to log in successfully. When I run my application from http://boebot.karleldridge.com I am not able to log in successfully, however, I am able t...

I cannot use SQL Server 2008 Express with Visual Web Developer 2008 Express
I have downloaded the Visual Studio 2008 Express with SP1 All-In-One DVD image file, burned a DVD and installed Visuall Basic, with SQL Server and Visual Web Developer. However when I try to test a new site and add a  New Item/SQL Sever database I get a message: "failed to generate a user instance of SQL Server due to failure in starting the process for the user instance. The connection will be closed." I tried to use SQL Server Configuration manager and change SQLEXPRESS logon properties (different built-in account and my Windows Vista account) but it doesn't work and I ...

Web resources about - Using ADO to connect to SQL Server 2005 or 2008 (Express and standard) using Windows Authentication - embarcadero.delphi.ado

Authentication - Wikipedia, the free encyclopedia
Authentication (from Greek : αὐθεντικός authentikos , "real, genuine," from αὐθέντης authentes , "author") is the act of confirming the truth ...

New Tools to Optimize App Authentication
At f8, we announced a redesigned Auth Dialog and a new authentication flow to give developers more control over people’s first experience with ...

Facebook Tells Some Developers They Have 48 Hours to Fix Authentication Data Leaks
... sent an email to what it calls a “very small percentage of the developer community” informing them their apps are suspected of leaking authentication ...

Lockdown - A better two-factor authentication experience on the App Store on iTunes
Get Lockdown - A better two-factor authentication experience on the App Store. See screenshots and ratings, and read customer reviews.


Sony Authentication Power Outlet Recognizes Users and Devices #DigInfo - YouTube
Sony Authentication Power Outlet Recognizes Users and Devices DigInfo TV - http://diginfo.tv 9/3/2012 NFC & Smart WORLD 2012 Sony Authentication ...

SafeNet brings Cloud-based authentication service to A/NZ
SafeNet has released its new Cloud-based authentication service, billed as Authentication-as-a-Service, in A/NZ.

Two-factor authentication - cyber security -
Two recent hacking cases highlight how personal emails can impact overall business security through tiny weaknesses.

Digital authentication to become Google's next big focus
Streamlining the website login process a top priority, according to the company’s Australian business and consumer services manager Dan Metcalf. ...

ATO boosts service access via app and voice authentication
The ATO has announced it will extend its voice authentication system to its mobile app

Resources last updated: 12/23/2015 7:45:36 PM