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 URL images

Hello,
I have a server that provides images (image server). These images are
accessed by my report. In order to improve the security access to the
images, I need to set the authentication on the image server. Reporting
Services sp1 doesn't support it (as declared) then I'm looking for an
alternative.
I thought to have a client certificate (where the report server is hosted)
and request a digital certificate to the image server. Is there any other
alternative ?
Thanks,
Pierre
--
---
Pierre Greborio
Microsoft .NET MVP
http://www.ugidotnet.org
http://www.amazon.com/infopath
---Hi Pierre:
Is it an Intranet environment? What about disabling anonymous access
to the directory with the images on the image server and requiring
windows authentication?
Just a thought,
--
Scott
http://www.OdeToCode.com
On Mon, 2 Aug 2004 10:31:14 +0200, "Pierre Greborio"
<pierreANTI_SPAM@.pierregreborio.it> wrote:
>Hello,
>I have a server that provides images (image server). These images are
>accessed by my report. In order to improve the security access to the
>images, I need to set the authentication on the image server. Reporting
>Services sp1 doesn't support it (as declared) then I'm looking for an
>alternative.
>I thought to have a client certificate (where the report server is hosted)
>and request a digital certificate to the image server. Is there any other
>alternative ?
>Thanks,
>Pierre|||Hi Scott,
> Is it an Intranet environment? What about disabling anonymous access
> to the directory with the images on the image server and requiring
> windows authentication?
I get a security error. It seems that the windows authentication isn't
supported. Is there any workaround ?
Thanks,
Pierre
>> Hello,
>> I have a server that provides images (image server). These images are
>> accessed by my report. In order to improve the security access to the
>> images, I need to set the authentication on the image server.
>> Reporting Services sp1 doesn't support it (as declared) then I'm
>> looking for an alternative.
>> I thought to have a client certificate (where the report server is
>> hosted) and request a digital certificate to the image server. Is
>> there any other alternative ?
>> Thanks,
>> Pierre
--
---
Pierre Greborio
Microsoft .NET MVP
http://www.ugidotnet.org
http://www.amazon.com/infopath
---|||Hi Pierre:
Off hand I cannot think of another way to protect images on the image
server - I think it's strange you'd get a security error enabling
windows authenticaiton but perhaps I'm not understanding the
environment you are in.
Another option would be to keep the images as blobs in the database,
then it could be setup so that only people with access to the report
will have access to the images.
--s
On Mon, 2 Aug 2004 16:54:42 +0200, "Pierre Greborio"
<pierreANTI_SPAM@.pierregreborio.it> wrote:
>Hi Scott,
>> Is it an Intranet environment? What about disabling anonymous access
>> to the directory with the images on the image server and requiring
>> windows authentication?
>I get a security error. It seems that the windows authentication isn't
>supported. Is there any workaround ?
>Thanks,
>Pierre
>> Hello,
>> I have a server that provides images (image server). These images are
>> accessed by my report. In order to improve the security access to the
>> images, I need to set the authentication on the image server.
>> Reporting Services sp1 doesn't support it (as declared) then I'm
>> looking for an alternative.
>> I thought to have a client certificate (where the report server is
>> hosted) and request a digital certificate to the image server. Is
>> there any other alternative ?
>> Thanks,
>> Pierre
--
Scott
http://www.OdeToCode.com|||Pierre,
Not sure what you mean by "I need to set the authentication on the image
server". In SP 1, image access is done under the context of a special
account. From the SP1 Readme:
"When the report is previewed in Report Designer, preview uses the
credentials of the user to display the image. When the report is run on the
report server, the report server uses the unattended execution account to
retrieve the image. If the unattended execution account is not specified,
the image is retrieved using no credentials (anonymous user account). If
either of these accounts have insufficient rights to access the image, the
image will not be displayed in the report. For more information about
setting the unattended execution account on the report server, see
"Configuring an Account for Unattended Report Processing" in Reporting
Services Books Online."
If you need more secure acces than this, you can try using a function for
the image source which will do the security check.
--
Hope this helps.
---
Teo Lachev, MCSD, MCT
Author: "Microsoft Reporting Services in Action"
http://www.prologika.com
"Pierre Greborio" <pierreANTI_SPAM@.pierregreborio.it> wrote in message
news:uLiNRqGeEHA.3148@.TK2MSFTNGP10.phx.gbl...
> Hello,
> I have a server that provides images (image server). These images are
> accessed by my report. In order to improve the security access to the
> images, I need to set the authentication on the image server. Reporting
> Services sp1 doesn't support it (as declared) then I'm looking for an
> alternative.
> I thought to have a client certificate (where the report server is hosted)
> and request a digital certificate to the image server. Is there any other
> alternative ?
> Thanks,
> Pierre
> --
> ---
> Pierre Greborio
> Microsoft .NET MVP
> http://www.ugidotnet.org
> http://www.amazon.com/infopath
> ---
>

Authenticating to Native SQL 2005 Web Services

Hi,
In a SOA architecture, is it possible for an Internet (NOT intranet) user
of a Windows Forms app to authenticate with the Native Web Services of SQL
Server 2005 using Windows Integrated Authentication? The users would NOT be
in the same domain or in a trusted domain. If so, could you point me to som
e
configuration instructions please.
Thanks.
--
JohnHello John,
Based on my scope, Integrated Windows Authentication works only in
scenarios where the client computer can contact a domain controller to
validate their credentials or keep a circuit open for point to point
connection to server. In most firewall configurations, this scenario is not
possible and not desirable. Though it is not IIS, the requirement are
similar:
IIS Authentication and Security for Internet Developers
http://msdn.microsoft.com/library/d...-us/dnauth/html
/dnauth_security.asp
264921.KB.EN-US INFO: How IIS Authenticates Browser Clients
http://support.microsoft.com/defaul...KB;EN-US;264921
Internet Information Services 5.0 Authentication Methods
http://www.windowsitpro.com/article...?articleid=8443
IIS 101: The Basics of IIS Authentication
http://www.iisadministrator.com/Art...ArticleID=15843
As for authentication of Web service in SQL 2005, it requests client to
send credential to server directly. Otherwise it fails.
Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
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.
| Thread-Topic: Authenticating to Native SQL 2005 Web Services
| thread-index: AcXeqMzup44GBeNiRta2Kazq0yKi6Q==
| X-WBNR-Posting-Host: 71.192.119.68
| From: "examnotes" <Jthayer@.online.nospam>
| Subject: Authenticating to Native SQL 2005 Web Services
| Date: Mon, 31 Oct 2005 21:55:02 -0800
| Lines: 9
| Message-ID: <E83A79C0-D7BB-4C3A-9435-166B06A081FA@.microsoft.com>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="Utf-8"
| Content-Transfer-Encoding: 7bit
| X-Newsreader: Microsoft CDO for Windows 2000
| Content-Class: urn:content-classes:message
| Importance: normal
| Priority: normal
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
| Newsgroups: microsoft.public.sqlserver.security
| NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
| Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGXA03.phx.gbl
| Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.security:6416
| X-Tomcat-NG: microsoft.public.sqlserver.security
|
| Hi,
| In a SOA architecture, is it possible for an Internet (NOT intranet)
user
| of a Windows Forms app to authenticate with the Native Web Services of
SQL
| Server 2005 using Windows Integrated Authentication? The users would NOT
be
| in the same domain or in a trusted domain. If so, could you point me to
some
| configuration instructions please.
| Thanks.
| --
| John
|

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 between Webserver and database Server

Not sure if this is an ASP.NET or a SQL issue...
Environment:
Webserver on Server1
SqlServer on Server 2
Both machines in the same domain. I have followed the steps in the to create
a domain level account to run the ASPNET process that has access to the
database.
When I try to retrieve data from the DB, i get:
Login failed for user '(null)'. Reason: Not associated with a trusted SQL
Server connection. 18452
Any guidence would be appriciated.See "Security Account Delegation" in BOL.
AMB
"PublicRick" wrote:

> Not sure if this is an ASP.NET or a SQL issue...
> Environment:
> Webserver on Server1
> SqlServer on Server 2
> Both machines in the same domain. I have followed the steps in the to crea
te
> a domain level account to run the ASPNET process that has access to the
> database.
> When I try to retrieve data from the DB, i get:
> Login failed for user '(null)'. Reason: Not associated with a trusted SQL
> Server connection. 18452
> Any guidence would be appriciated.
>|||http://msdn.microsoft.com/library/default.asp?
AND
http://msdn.microsoft.com/library/d...
d19.asp
HTH, Jens SUessmeyer.
"PublicRick" <publicrick@.nospam.nospam> schrieb im Newsbeitrag
news:EE873E20-AA3E-41B5-9030-9F0A2C360A41@.microsoft.com...
> Not sure if this is an ASP.NET or a SQL issue...
> Environment:
> Webserver on Server1
> SqlServer on Server 2
> Both machines in the same domain. I have followed the steps in the to
> create
> a domain level account to run the ASPNET process that has access to the
> database.
> When I try to retrieve data from the DB, i get:
> Login failed for user '(null)'. Reason: Not associated with a trusted SQL
> Server connection. 18452
> Any guidence would be appriciated.
>

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!

Authenticating a report render from a browser

I am copying the question from another member as it is exactly my problem:
We have an extranet application (internet users) written in ASP using
VB COM. All the security of what reports users can see is handled
through this application. Is there anyway we can link RS reports using
a URL? In otherwords, users will login like they currently do now and
our application will provide a report list for them. When they run a
report, they will basically click on a link that points to the RS
report. Ideally, when the user logs in initially, they will be
validated by the current application and then be validated against RS
(background). We need this to be as transparent as possible.
I have seen this posted in several different ways, but without any
resolution. Is there no way to accomplish this?
Thanks.we have the same situation. One way to do it is to create a custom
security extension for RS and call it through web services SOAP. You
can either redirect to an aspx page that calls it or maybe call the
webservice in your asp page directly. You should read this article as
it explains it all http://www.devx.com/dotnet/Article/26759|||Marv,
I appreciate the reply and have checked out the web site you referenced. I
hate to be a wimp, but I'm kind of a one-man band and don't have a lot of
time (or a lot of the requisite experience) to undertake a project for what I
was hoping to be a simple solution.
I take it there is just no simple way to tell the Report Server to run the
report on request without additional authentication. It seems like there
should be some setting either in the manager or on the command line that
could accomplish this. I have tried various role assignments, but they seem
to be limited to the server on which the RS resides and all our user
authenication comes from a different domain (and we don't use active
directory).
Anyway, thanks for the response.
Dave
"Marv" wrote:
> we have the same situation. One way to do it is to create a custom
> security extension for RS and call it through web services SOAP. You
> can either redirect to an aspx page that calls it or maybe call the
> webservice in your asp page directly. You should read this article as
> it explains it all http://www.devx.com/dotnet/Article/26759
>|||"Dave" wrote:
> I take it there is just no simple way to tell the Report Server to run the
> report on request without additional authentication.
Without getting into the benfits of ADS, the security model and the
vulnerabilities, etc., if you supply (or remove) the necessary credentials to
the data source and can render the report; you could display the report in an
iframe. It would appear as though it was part of your existing application.
src=http://someserver/reportserver?/FolderName/Report&rs:Command=Render&rc:toolbar=false
Caveat emptor. Someone will find the links.
Just a thought.