SQL Security - Windows network through ASP pages 1

[firsttube]

I have a web application in ASP that updates a table in a SQL Server database. We have a windows network here. Is there any connection string for ADO using OLEDB that will persist the windows login information through to SQL Server?

This does not seem to work, even though the user is registered on the server and has access to the database. It seems that SQL Server sees it as an anonymous login attempt:

Driver={SQL Server};Server=ServerName;Database=database;Uid=user;Pwd=pass;"

Information is not Knowledge, Knowledge is not Wisdom, Wisdom is not Truth, Truth is not Beauty, Beauty is not Love, Love is not Music, Music is the best.

 

[ptheriault]

(I may be wrong with this) but...
I think you want to use something like -T instead of user and password. The -T means trusted.

- Paul

- If at first you don't succeed, find out if the loser gets anything.

 

[mrdenny]

-T is the switch for osql, sqlcmd, etc.

This site has most of the options.

http://www.connectionstrings.com/

Here is the string for a trusted connection.

Driver={SQL Server};Server=myServerAddress;Database=myDataBase;Trusted_Connection=Yes;

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[ptheriault]

Thanks for that Denny,
I knew it had to be trusted I just wasn't sure of the correct syntax.

- Paul

- If at first you don't succeed, find out if the loser gets anything.

 

[firsttube]

Thanks for the suggestion. I tried usine Trusted_Connection=Yes, but got this error:

Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'

any thoughts?

Information is not Knowledge, Knowledge is not Wisdom, Wisdom is not Truth, Truth is not Beauty, Beauty is not Love, Love is not Music, Music is the best.

 

[firsttube]

I found the identity of the application pool that is running in IIS that the asp page is part of. Should this identity be allowed access to the SQL Server?


Information is not Knowledge, Knowledge is not Wisdom, Wisdom is not Truth, Truth is not Beauty, Beauty is not Love, Love is not Music, Music is the best.

 

[mrdenny]

Yes, that identity should be a domain account, and that account should be granted rights into the SQL Server. The rights granted should be the minimum rights needed for the web page to do what it needs to do. In order words don't give it sysadmin rights.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[firsttube]

that makes sense. But if I use a specific user name and password that I set up on SQL Server, and give that user update rights to a table it works from the asp page, without giving the identity access rights. It seems it is only when I try to use a specific user account that is a windows login account I get this error.


Information is not Knowledge, Knowledge is not Wisdom, Wisdom is not Truth, Truth is not Beauty, Beauty is not Love, Love is not Music, Music is the best.

 

[mrdenny]

Correct, because when you use the SQL Account, you are using that account to log into the database, not the windows account.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[firsttube]

I gave the identity for the application access rights to the server and to the sql database and I still have the same error on the asp page. Any thoughts?

I really want to be able to use windows accounts to control the permissions through the asp page using SQL server user access.

Information is not Knowledge, Knowledge is not Wisdom, Wisdom is not Truth, Truth is not Beauty, Beauty is not Love, Love is not Music, Music is the best.

 

[mrdenny]

You might want to check in the IIS forum.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com