authentication/db connection issues with new setup

Hi,
I've pretty much just thrown together a reporting services (2005)
configuration which connects to a mature sql server 2000 database held
elsewhere. Forgive me for probably not having too much of a clue
about how things should be set up, but I had a go.
I'll get straight to the problem.. that is I can view reports from the
machine running the server, using my own credentials or those of
someone else (tested by running IE7 under an alternative account on
the domain).
However, when using those same user credentials but from a different
machine, I get the following error:
An error has occurred during report processing.
Cannot create a connection to data source 'dsTachyon'.
Login failed for user '(null)'. Reason: Not associated with a trusted
SQL Server connection.
Any ideas as to what would be causing this?
cheers,
ChrisMy guess is that it's a "double hop" authentication problem.
You can connect directly to the db when you're on the server, which is a
direct connection. But when you're trying to connect to the db through the
report server, you have one connection hop from your pc to the report
server, and one hop from the report server to the db server. This is called
a double hop.
There are two ways of fixing it.
1) You can connect to the db with a static account that has read access to
the db you get your data from. Instead of using Windows Integrated
Authentication, you use "Credentials stored securely in the report server".
This can be either a SQL account or a Windows account. If you use this, make
sure you're encrypting the data in your report database, in order to encrypt
the password you add.
2) Configure the report server and the db server to use Kerberos, to allow
the credentials to be sent from your pc through the report server to the db
server.
If you want to use Kerberos, start by reading
Configuring Authentication for Reporting Services
http://msdn2.microsoft.com/en-us/library/bb283249.aspx
Specifying Credential and Connection Information
http://msdn2.microsoft.com/en-us/library/ms160330.aspx#
And
Configuring Constrained Delegation for Kerberos (IIS 6.0)
http://www.microsoft.com/technet/prodtechnol/WindowsServer2003/Library/IIS/df979570-81f6-4586-83c6-676bb005b13e.mspx?mfr=true
You also need to make sure Anonymous Access to the Report Server web
application is not allowed.
My suggestion is to first see if you're able to connect to the data with the
first setup (using a static account). If it works, you should use Kerberos,
as this is a more secure solution. If it doesn't work with a static account,
you might want to work out why before setting up Kerberos, because it's
usually easier, but less secure to make it work with a static account.
Kaisa M. Lindahl Lervik
"Not Me" <clhumphreys@.gmail.com> wrote in message
news:1174310214.836485.140910@.n59g2000hsh.googlegroups.com...
> Hi,
> I've pretty much just thrown together a reporting services (2005)
> configuration which connects to a mature sql server 2000 database held
> elsewhere. Forgive me for probably not having too much of a clue
> about how things should be set up, but I had a go.
> I'll get straight to the problem.. that is I can view reports from the
> machine running the server, using my own credentials or those of
> someone else (tested by running IE7 under an alternative account on
> the domain).
> However, when using those same user credentials but from a different
> machine, I get the following error:
> An error has occurred during report processing.
> Cannot create a connection to data source 'dsTachyon'.
> Login failed for user '(null)'. Reason: Not associated with a trusted
> SQL Server connection.
> Any ideas as to what would be causing this?
> cheers,
> Chris
>|||On 19 Mar, 14:00, "Kaisa M. Lindahl Lervik" <kais...@.hotmail.com>
wrote:
> My guess is that it's a "double hop" authentication problem.
> You can connect directly to the db when you're on the server, which is a
> direct connection. But when you're trying to connect to the db through the
> report server, you have one connection hop from your pc to the report
> server, and one hop from the report server to the db server. This is called
> a double hop.
Thank you! Great depth to your reply, I've tried the static account
solution and that works so I'll give Kerberos a look.
Cheers,
Chris

authentication with mssql server

Currently all of our MS SQL Server databases have been installed so as once an administrator connects to the server they can get access to the SQL Server database. We would like to change this to ensure that no matter who you are you have to enter a valid username and password in SQL Server to connect to the database. I cannot seem to find out how to do this. All help will be greatly appreciated.

Thanks
SamRefer to books online for AUTHENTICATION MODES & other related topics which covers what you're looking for. Adopt Mixed mode authentication if you would like to enter user/password during the connection.

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!

auditing no of connections made to the database

Hi

My application connects to 3 databases .

I need to know how many connections are made by my application to each database

There are two applications servers. Basically what i need is to know how many connection to each database from each application server.

(that is how many connections from application server 1 to database 1,2 &3 and how many connections from applications server 2 to database 1 ,2 & 3)

Can this be done ..?

How to do it ?

Please help me in regard to this

Thanks in advance.

I would have a look at SQL Profiler, with this tool you will be able to run a trace on each of the databases and then count the connections to the database.

|||

Hi

Thanks for the reply.

I need to know whether is there any parameter which i can use to monitor the same.

i suppose @.@.connections gives me the number of connections made to the server and not the databases. ?

Is there any parameter in SQL 2005 which i can use to monitor.

Please help....

Thanks in advance.

|||

There is no parameter that you can use, but you could tag into the performance counters on the machine, or set up a view or function to sort the activity tables in the master database... Have a look at the stored proc sp_who2.

|||

In SQL Server 2000 Enterprise Manager, open Management/Current Activity/Process Info and look for the logins the app uses (under column "User") and the databases (under column Database). In this way you can identify the connections. (Each process is a connection.) "Host" column may also help, this is the name of computer the application is running on.

In SQL Server 2005 Management Studio its similar, except you open Management/Activity Monitor.

|||In SQL Server 2000, query sysprocesses (e.g., select * from sysprocesses). Use loginname or nt_username to specify the login the app uses. You may want to translate dbid to the database name, sysdatabases gives you the "name" for each "dbid".