Authentication to SQL Server in ASP on Active Directory

I would like to be able to autheticate users connecting to SQL Server in
Active Server Pages on MS Active Directory.
Is this possible? If so, how?
Thank you.
AAAHi,
then you have to Map AD user account / group to sql server then you are
able to do it . please refer sp_adduser for more information it can be done
very well using this sp it is used to map those Windows (AD) accounts to SQL
and then use it in you ASP connection string. What is your Authentication
mode ?!
--
Andy Davis
Active Crypt Team
---SQL Server Encryption
Decryption Software
http://www.activecrypt.com
"AuntieAuntieAuntie" wrote:

> I would like to be able to autheticate users connecting to SQL Server in
> Active Server Pages on MS Active Directory.
> Is this possible? If so, how?
> Thank you.
> AAA|||Hi Andy,
This is the situation, (we can add users) but, not Domain Accounts that
belong to Domain Groups without a password on the connection string, within
an ASP on Active Directory, this is my current connection string:
<%
Dim strDatabaseType, objConn, cst
cst = "Provider=SQLOLEDB;data source=Z0123456;" &_
"Database=myDatabase;User ID=99;password=XXXXXXX;"
Set objConn = Server.CreateObject("ADODB.Connection")
%>
We would like to use a Domain Account as the userID without the password on
this connection string. On the database side, this account belongs to a
Domain Group, that has access to the database, it this possible? How can we
accomplish it?
AAA
"Andy Davis" wrote:
[vbcol=seagreen]
> Hi,
> then you have to Map AD user account / group to sql server then you are
> able to do it . please refer sp_adduser for more information it can be don
e
> very well using this sp it is used to map those Windows (AD) accounts to S
QL
> and then use it in you ASP connection string. What is your Authentication
> mode ?!
> --
> Andy Davis
> Active Crypt Team
> ---SQL Server Encryption
> Decryption Software
> http://www.activecrypt.com
>
> "AuntieAuntieAuntie" wrote:
>|||Hi,
We are using Windows Authentication for this applicaton.
AAA
"Andy Davis" wrote:
[vbcol=seagreen]
> Hi,
> then you have to Map AD user account / group to sql server then you are
> able to do it . please refer sp_adduser for more information it can be don
e
> very well using this sp it is used to map those Windows (AD) accounts to S
QL
> and then use it in you ASP connection string. What is your Authentication
> mode ?!
> --
> Andy Davis
> Active Crypt Team
> ---SQL Server Encryption
> Decryption Software
> http://www.activecrypt.com
>
> "AuntieAuntieAuntie" wrote:
>

Authentication question

I have a question about authentication with SQL 2005. I want to create a
group in our Active Directory (Win 2003) and make it a user in MS SQL 2005.
Would users of the AD group then be able to authenticate to the SQL Server
because the group is in SQL Server or not?You need to connect to your instance and create a login principal from the
Security for the mentioned Windows Group and map it to whatever resource
they need to reach.
To achieve this, your SQL Server server also has to be joined to the
mentioned domain.
--
Ekrem Önsoy
"Tom Reis" <reistom@.cdnet.cod.edu> wrote in message
news:uTlojAEAIHA.536@.TK2MSFTNGP06.phx.gbl...
>I have a question about authentication with SQL 2005. I want to create a
>group in our Active Directory (Win 2003) and make it a user in MS SQL 2005.
>Would users of the AD group then be able to authenticate to the SQL Server
>because the group is in SQL Server or not?
>

Authentication Issue

We have a sql 2005 server that we need to connect to.
I am in one active directory domain (eg DomainA) and the sql 2005 server is in another (eg DomainB).

We are not allowed (by the sql 2005 server provider) to put a trust between the two active directory domains and sql server logins present an issue due to mirroring (this is not a question about mirroring).

Is there a way of logining the EnterpriseManager/QueryAnalyser applications in to the sql server 2005 in DomainB with out logging my whole machine into DomainB and with out using SQL server authentication.

Any help would be greatly apperiated

Hi,

Stored credentials can be found navigating to the to the Control Panel > User Accounts > Choose the User > manage you network accounts. You might have a fixed stored credential enetered there ?

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

Authenticating users with Active Directory and SQL Server 2000

I've set up a web page on our network that links to an SQL Server database c
ontaining records of staff members. At the moment the web page extracts all
the rows from the table and shows all the staff records.
I want to be able to authenticate staff members using Active Directory, so w
hen they log onto their PC and access the webpage on our intranet they can O
NLY view their record from the database.
I'm just starting out with SQL Server and Active Directory, so any help woul
d be appreciated. From what I have read I think that I may need to use LDAP
to retrieve the Active Directory information.
Thanks in advance for your help.Use Windows Authentication for your web site, which will authorize the user
through AD. Then use integrated security/impersonation to pass the user
credentials to SQL for authorization and connection. Then, you can use the
USER_NAME (or similar technique) to filter the rows returned by any SQL
statement.
Hope this helps you get started...
"Enterprise Andy" <EnterpriseAndy@.discussions.microsoft.com> wrote in
message news:CBD3FEF9-ACF1-4979-9A90-4B4AF44B23C9@.microsoft.com...
> I've set up a web page on our network that links to an SQL Server database
containing records of staff members. At the moment the web page extracts all
the rows from the table and shows all the staff records.
> I want to be able to authenticate staff members using Active Directory, so
when they log onto their PC and access the webpage on our intranet they can
ONLY view their record from the database.
> I'm just starting out with SQL Server and Active Directory, so any help
would be appreciated. From what I have read I think that I may need to use
LDAP to retrieve the Active Directory information.
> Thanks in advance for your help.

Authenticating from Active Directory Domain

We use a query that gets the suser_name parameter and
inserts it into a table.
A user converted to an Active Directory Domain and the
suser_name comes up as null.
Is this the way it works? Or is there some setting that
has to be changed?
Any help appreciated.
Thanks,
GeorgeDirectly from BOL
suser_name is obsolete and always returns NULL in S2K.
"George Galcik" <anonymous@.discussions.microsoft.com> wrote in message
news:005b01c3dab4$6bf0fdb0$a101280a@.phx.gbl...

quote:

---

> We use a query that gets the suser_name parameter and
> inserts it into a table.
> A user converted to an Active Directory Domain and the
> suser_name comes up as null.
> Is this the way it works? Or is there some setting that
> has to be changed?
> Any help appreciated.
> Thanks,
> George
>

---

|||Thanks for your help, Scott.
George

quote:

---

>--Original Message--
>Directly from BOL
>suser_name is obsolete and always returns NULL in S2K.
>"George Galcik" <anonymous@.discussions.microsoft.com>

---

wrote in message

quote:

---

>news:005b01c3dab4$6bf0fdb0$a101280a@.phx.gbl...
>
>.
>

---

Authenticating against LDAP/Active Directory with SQL Server 2000 SP2

Hello everyone,

We have a custom application that connects remotely to a SQL Server 2000 (SP2) database. We would like our application to validate a user's login against Active Directory.

So far I have been able to get a lookup working, but I can not find documentation on how to validate the password from within SQL Server. I found a lot of notes on using ASP.NET objects, or VB, C#, etc, but for this solution it must be done entirely in SQL. It would also be nice if this were SQL Server 2005; we could just embed the C# code and call it as a SQL stored proc, but unfortunately we are left with the constraint that we cannot upgrade this DB at this time.

Linked server 'ADSI' is set up with the sp_addlinkedserver command:

EXEC master.dbo.sp_addlinkedserver @.server = N'ADSI', @.srvproduct=N'Active
Directory Services 2.5', @.provider=N'ADsDSOObject', @.datasrc=N'adsdatasource'

Two table functions in our test DB (trying to test both ways I've found in docs):

ALTER FUNCTION [dbo].[GetAuthenticatedUserViaLDAP]
(
-- Add the parameters for the function here
@.userId nvarchar(50),
@.password nvarchar(50)
)
RETURNS TABLE
AS
RETURN
(
-- Add the SELECT statement with parameter references here
SELECT [SAMAccountName], CN [Name], SN [Last Name], ST State
FROM OPENQUERY( ADSI,
'<LDAP://DC=company,DC=com>;((objectClass=user));SAMAccountName,cn,sn,st')
WHERE [SAMAccountName] = @.userId
)

ALTER FUNCTION [dbo].[GetAuthenticatedUser]
(
-- Add the parameters for the function here
@.userId nvarchar(50),
@.password nvarchar(50)
)
RETURNS TABLE
AS
RETURN
(
-- Add the SELECT statement with parameter references here
SELECT [SAMAccountName], [Name], SN [Last Name], ST State
FROM OPENQUERY( ADSI,
'SELECT SAMAccountName, Name, SN, ST
FROM ''LDAP://bdsserver1/ CN=users,DC=company,DC=com''
WHERE objectCategory = ''Person''
AND objectClass = ''user'' ')
WHERE [SAMAccountName] = @.userId
)

So calling either of these table functions from our custom application gives the same result:

select * from dbo.GetAuthenticatedUser('astonaker','abc')
OR
select * from dbo.GetAuthenticatedUserViaLDAP('astonaker','abc')

ResultSet:

'astonaker', 'Anthony', 'Stonaker' 'NULL'

So I can at least tell if a given user exists or not, but I have no visibility into whether the password they entered into our application is valid in LDAP.

I don't want to pass unencrypted passwords through the network, but then I don't know how to encrypt/compare these passwords without using the .NET Connection or DirectoryEntry, etc objects.

Any thoughts/suggestions are greatly appreciated!

Why do you need to validate credentials on a SQL server?

If a password is entered on a client, then could you validate it there or are you going to do something as that user on a SQL server?

In that case could you impersonate a user on a client and connect to the server?

|||The app that needs this validation cannot validate against LDAP directly; it is a limited custom-built scripting language. It can easily execute queries against its remote SQL Server DB, and we are trying to investigate a solution that will allow us to run a query to validate against LDAP from there. Simply knowing whether or not the user ID/password combination entered into the app matches the Active Directory credentials is sufficient.

Edit: From browsing more formus/docs today, what do you think of using an extended stored procedure? Possibly compile the .NET connection code in a dll and plug it into sql server? From digging through more postings it looks like it might be the most direct route I'll be able to get.

Thanks for the post!
|||That extended procedure did the trick!

We ended up using IADsOpenDSObject:: OpenDSObject for this purpose. We were constrained to using C/C++ and none of the .NET classes, but there was very nice documentation in several places that served as great guides:

http://www.codeproject.com/database/extended_sp.asp
http://msdn2.microsoft.com/en-us/library/aa706065.aspx

I did see where MS noted that "This method should not be used just to validate user credentials," but the link they provided to SSPI authentication was not very helpful.

ALSO, for anyone else jumping through the same hoops, MS noted that the function srv_describe (and its companion srv_ functions) is being removed in future versions of SQL Server:

http://msdn2.microsoft.com/en-us/library/ms164631.aspx

They state to "Use CLR Integration instead," so if anyone has any links to a good reference I'd love to check them out!

Authenticating against LDAP/Active Directory with SQL Server 2000 SP2

Hello everyone,

We have a custom application that connects remotely to a SQL Server 2000 (SP2) database. We would like our application to validate a user's login against Active Directory.

So far I have been able to get a lookup working, but I can not find documentation on how to validate the password from within SQL Server. I found a lot of notes on using ASP.NET objects, or VB, C#, etc, but for this solution it must be done entirely in SQL. It would also be nice if this were SQL Server 2005; we could just embed the C# code and call it as a SQL stored proc, but unfortunately we are left with the constraint that we cannot upgrade this DB at this time.

Linked server 'ADSI' is set up with the sp_addlinkedserver command:

EXEC master.dbo.sp_addlinkedserver @.server = N'ADSI', @.srvproduct=N'Active
Directory Services 2.5', @.provider=N'ADsDSOObject', @.datasrc=N'adsdatasource'

Two table functions in our test DB (trying to test both ways I've found in docs):

ALTER FUNCTION [dbo].[GetAuthenticatedUserViaLDAP]
(
-- Add the parameters for the function here
@.userId nvarchar(50),
@.password nvarchar(50)
)
RETURNS TABLE
AS
RETURN
(
-- Add the SELECT statement with parameter references here
SELECT [SAMAccountName], CN [Name], SN [Last Name], ST State
FROM OPENQUERY( ADSI,
'<LDAP://DC=company,DC=com>;((objectClass=user));SAMAccountName,cn,sn,st')
WHERE [SAMAccountName] = @.userId
)

ALTER FUNCTION [dbo].[GetAuthenticatedUser]
(
-- Add the parameters for the function here
@.userId nvarchar(50),
@.password nvarchar(50)
)
RETURNS TABLE
AS
RETURN
(
-- Add the SELECT statement with parameter references here
SELECT [SAMAccountName], [Name], SN [Last Name], ST State
FROM OPENQUERY( ADSI,
'SELECT SAMAccountName, Name, SN, ST
FROM ''LDAP://bdsserver1/ CN=users,DC=company,DC=com''
WHERE objectCategory = ''Person''
AND objectClass = ''user'' ')
WHERE [SAMAccountName] = @.userId
)

So calling either of these table functions from our custom application gives the same result:

select * from dbo.GetAuthenticatedUser('astonaker','abc')
OR
select * from dbo.GetAuthenticatedUserViaLDAP('astonaker','abc')

ResultSet:

'astonaker', 'Anthony', 'Stonaker' 'NULL'

So I can at least tell if a given user exists or not, but I have no visibility into whether the password they entered into our application is valid in LDAP.

I don't want to pass unencrypted passwords through the network, but then I don't know how to encrypt/compare these passwords without using the .NET Connection or DirectoryEntry, etc objects.

Any thoughts/suggestions are greatly appreciated!

Why do you need to validate credentials on a SQL server?

If a password is entered on a client, then could you validate it there or are you going to do something as that user on a SQL server?

In that case could you impersonate a user on a client and connect to the server?

|||The app that needs this validation cannot validate against LDAP directly; it is a limited custom-built scripting language. It can easily execute queries against its remote SQL Server DB, and we are trying to investigate a solution that will allow us to run a query to validate against LDAP from there. Simply knowing whether or not the user ID/password combination entered into the app matches the Active Directory credentials is sufficient.

Edit: From browsing more formus/docs today, what do you think of using an extended stored procedure? Possibly compile the .NET connection code in a dll and plug it into sql server? From digging through more postings it looks like it might be the most direct route I'll be able to get.

Thanks for the post!
|||That extended procedure did the trick!

We ended up using IADsOpenDSObject:: OpenDSObject for this purpose. We were constrained to using C/C++ and none of the .NET classes, but there was very nice documentation in several places that served as great guides:

http://www.codeproject.com/database/extended_sp.asp
http://msdn2.microsoft.com/en-us/library/aa706065.aspx

I did see where MS noted that "This method should not be used just to validate user credentials," but the link they provided to SSPI authentication was not very helpful.

ALSO, for anyone else jumping through the same hoops, MS noted that the function srv_describe (and its companion srv_ functions) is being removed in future versions of SQL Server:

http://msdn2.microsoft.com/en-us/library/ms164631.aspx

They state to "Use CLR Integration instead," so if anyone has any links to a good reference I'd love to check them out!

Authenticate via Active Directory from PC that's not joined the do

I have several people on my team that are working remotely from PCs that are
stand-alone, ie. they have not been joined to the domain. They do have an
account on the domain, though, which they use to map drives and such. How
can I set it up so that they can use Enterprise Manager or Query Analyzer by
authenticating with their domain account?Hello,
I suggest that you create a SQL login for each user. You can refer to the
following article:
Adding a SQL Server Login
http://msdn.microsoft.com/library/d...-us/adminsql/ad
_security_9m0e.asp
How to set up Mixed Mode security (Enterprise Manager)
http://msdn.microsoft.com/library/d...-us/howtosql/ht
_6_secrty_68c9.asp
Make sure SQL server use mixed mode Authentication.
If the client can join the domain with a domain user account, you can add
the domain user account as a Windows login. You can refer to the following
web site:
How to grant a Windows user or group login access to SQL Server (Enterprise
Manager)
http://msdn.microsoft.com/library/d...-us/howtosql/ht
_6_secrty_68c9.asp
Granting a Windows User or Group Access to a Database
http://msdn.microsoft.com/library/d...-us/adminsql/ad
_security_2wit.asp
I hope the information is helpful.
Sophie Guo
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
========================================
=============
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.

Authenticate against Active Directory

We are writing an enterprise application in which the user logs in by entering their username and password in a form. The user will enter their actual Active Directory credentials. I want to pass the username and password to a stored procedure that will check for a valid user account in an Active Directory. In Sql Server 2005 I know I could write a small .NET library that uses System.DirectoryServices , register it in SQL and access it from an stored procedure.

Can it be done in Sql server 2000?

The reason we are not just autheticating in the .NET code of the application is that we found that it does not work on Windows 98 cleints, hence the need to go through sql server.

Thanks

In SQL Server 2000, you can write an extended procedure. See http://msdn.microsoft.com/library/default.asp?url=/library/en-us/createdb/cm_8_des_07_9rxv.asp for additional information on extended stored procedures.

Thanks
Laurentiu

Attaching previous database to new installation

I have a Active/Passive cluster and have installed the SQL 2005.
What I would like to know, before I proceed with my configurations (since it
is my first time in a cluster environment) is whether I will need to attach
the database (lets call it DBX) to SQL Server on both the servers seperately,
or do i only have to attach the database DBX to SQL Server on the active
machine.
Like i said this is my first time so i want to be very sure before i proceed.
You don't attach the database to any node. Rather, you attach it to the SQL
Server instance regardless which node the instance may be running at the
time. In general, a SQL Server operation doesn't care where the SQL instance
is running.
Linchi
"iqv1" wrote:

> I have a Active/Passive cluster and have installed the SQL 2005.
> What I would like to know, before I proceed with my configurations (since it
> is my first time in a cluster environment) is whether I will need to attach
> the database (lets call it DBX) to SQL Server on both the servers seperately,
> or do i only have to attach the database DBX to SQL Server on the active
> machine.
> Like i said this is my first time so i want to be very sure before i proceed.
|||thanks for you advice Linchi... I will try that
"Linchi Shea" wrote:
[vbcol=seagreen]
> You don't attach the database to any node. Rather, you attach it to the SQL
> Server instance regardless which node the instance may be running at the
> time. In general, a SQL Server operation doesn't care where the SQL instance
> is running.
> Linchi
> "iqv1" wrote:
|||Keep in mind that SQL Server will only be active on one node at a time. The
disks will only be visible from this active node; so, that is where you must
place the database files.
Then, as Linchi stated, you must run the attach process through the SQL
Server instance, just like you would on a stand-alone system.
You connect to the SQL Server instance through the failover cluster server
name dedicated for SQL Server, not specific node names.
Sincerely,
Anthony Thomas

"iqv1" <iqv1@.discussions.microsoft.com> wrote in message
news:81F95D4E-ACAE-4C99-9144-A91D963A83BD@.microsoft.com...[vbcol=seagreen]
> thanks for you advice Linchi... I will try that
> "Linchi Shea" wrote:
SQL[vbcol=seagreen]
instance[vbcol=seagreen]
(since it[vbcol=seagreen]
attach[vbcol=seagreen]
seperately,[vbcol=seagreen]
active[vbcol=seagreen]
proceed.