Auto deletion of records sqlserver

Hi

I am not sure if I am at right place, anyhow I hope I am :)

Now the question: I am using an ASP.net Application with SQL-Server. I want to make a page so that it set the expiration time (date) for certain record and once that time reaches, it deletes those records, or make any updates to the record (what ever applicable). I also want to control this auto deletion from my application, means that turn this On/Off whenever needed. I am not sure how to start this. I was told by a friend that I need to use triggers from SQL-server but I need some help. Can anyone help me out on this?

Regards
Mykhan

A trigger will not expire the record; you will need to create a windows service or create a SQL job to delete the expired records. Alternatively you could add the following clause to your list select

AND (ExpiryDate is Null OR ExpiryDate > GetDate())

The latter would not delete the record but would achieve the same effect.

sql

Authorization problems with dso and asp (vbscript)

Hi all,
I have an authorization problem while trying to access to Metadata informations of Analysis Services via DSO with vbscript over IIS.
Calling from Browser an ASP page with the following content:
---
dim dsoServer, dsoDatabase, dsoCube
set dsoServer = Server.CreateObject ("DSO.Server")
dsoServer.Connect ("<myServerName>")
...
--
produces the following error:
Unable to connect to the registry on the server (DSCWDMT), or you are not a member of
the OLAP Administrators group on this server.

I found in this forum a similar post, and its proposed solution (apply sp1 to AS) will not work, because I have already SP1 installed (see also article Q297232 on MSDN).

I did the following tests:
1) run examples on \Programs\Microsoft Analysis Services\Samples
Yes, they work, but without DSO. I need DSO
2) Build a dll and register it for wwwroot.
Same error message
3) follow instruction on Q224973 (MSDN)
Same error message

I have the suspect, that the installation and/or the authorizations on W2K are not correct, but I do not know what.
I run SQL2K with unthrusted authorization (i.e. with user logon and password). This use is also allowed to see cubes (at least SELECT) in Analysis services. MDX Commands work fine and I get results.

So the question is: which user is trying to access DSO Services via Internet ?
if it is IUSR_<ServerName>, than it cannot work, because it has only guest auth. on ServerName, and it would not be correct to change it.

Do you have any hint ?

Background Infos:
OS: W2K with sp2
IS: IIS 5
Database: MS SQL2K with sp1
AS: SP1
Language: Visual Basic scripting edition (VBSCRIPT)

Thanks in advance
MatteoHi,

Remember that using Windows 2000 and your web server - IIS 5.0 - all connections and all things that you use from WEB use the default Windows 2000 user account that's IIS_<machine name>...

For example, if you must use a different Regional Settings for your WEB Aplications - ASP - and set it on the server by <b>Administrator</b> account, nothing will be changed by WEB users but the account used to local permissions on IIS is the IIS account ( IIS_<machine name> or IWS_<machine name> - I don't remember now ) .

[ ]'s|||Yes, this is correct. Everything works under the account IUSR_<MachineName>.
The problem ist meanwhile solved.
The error message came because IIUSR_<MachineName> could not read Registry Entries for HKLM/SOFTWARE/Microsoft/OLAP Server/...
So I added IUSR_<MachineName> wit readonly authorisation. This solved the problem.
Anyway this cannot be a good solution, because You change punctually something without a general application rule.
So I keep on searching.

Thanks,
Matteo

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 to SQL Server 2000 from ASP

please let me know if I need to post this on another SQL forum, and I'll be glad to do so.

I'm trying to set up the equivalent of Integrated Security=SSPI for SQL Server 2000 on IIS5.0. It's my understanding the SSPI string above is for Win NT, but not for Active Dir 2000 domains.

I'm using ASP 2.0 (NOT .Net), so please apprise me to what info I'd need in the connection string.

Currently it reads like this:

Set cn = CreateObject("ADODB.Connection")
cn.CursorLocation = 3 'Client side cursor
cn.Open "Provider=SQLOLEDB;Data Source=191.181.92.73;Integrated Security=SSPI"

Any feedback is welcomed.

Thanks!

Are the WebServer and the SQL Server on the same machine ? Did you enable Windows Authentication for the Website ?

Jens K. Suessmeyer

http://www.sqlserver2005.de
|||Any updates on that, or is the problem solved ?

Jens K. Suessmeyer.

http://www.sqlserver2005.de

Authentication over the internet

I am building an asp.net app that will use reporting services to show reports within the application. Users login to the application and when they need to see a report I use web services to render the report. The asp.net app and reporting services are on the same windows 2003 server (not using active directory).
Because reporting services uses Windows authentication and does not allow anonymous access, I have created a windows account (called "RSUser") that has access to my reports. When the user runs a report, I pass in the credentials for this windows account like this...
rs.Credentials = New System.Net.NetworkCredential("RSUser", "password", "domain")
This all works, and the report renders using the permissions from RSUser. The problem is that all the reports use treeviews for drill-down (and some use drill-through). When you expand a drill down you are prompted for a windows login. I think this is because this postback is now coming from the client PC, instead if from the asp.net app (i.e. on the server), and so reporting services needs to anthenticate this new user.
The only solution that I have found for this is developing a security extension for reporting services...
http://msdn.microsoft.com/library/?url=/library/en-us/dnsql2k/html/ufairs.asp?frame=true#ufairs_topic3
... but this seems like overkill and a very complicated process, and Microsoft says in the article that this is not fully tested and should not be used in a production environment (but that where I need it for).
Does anyone have a solution ?
Craig HBJust a thought: Have you tried to setup a individual Application pool that works with your RSUser Account?
"Craig HB" wrote:
> I am building an asp.net app that will use reporting services to show reports within the application. Users login to the application and when they need to see a report I use web services to render the report. The asp.net app and reporting services are on the same windows 2003 server (not using active directory).
> Because reporting services uses Windows authentication and does not allow anonymous access, I have created a windows account (called "RSUser") that has access to my reports. When the user runs a report, I pass in the credentials for this windows account like this...
> rs.Credentials = New System.Net.NetworkCredential("RSUser", "password", "domain")
> This all works, and the report renders using the permissions from RSUser. The problem is that all the reports use treeviews for drill-down (and some use drill-through). When you expand a drill down you are prompted for a windows login. I think this is because this postback is now coming from the client PC, instead if from the asp.net app (i.e. on the server), and so reporting services needs to anthenticate this new user.
> The only solution that I have found for this is developing a security extension for reporting services...
> http://msdn.microsoft.com/library/?url=/library/en-us/dnsql2k/html/ufairs.asp?frame=true#ufairs_topic3
> ... but this seems like overkill and a very complicated process, and Microsoft says in the article that this is not fully tested and should not be used in a production environment (but that where I need it for).
> Does anyone have a solution ?
> Craig HB|||Craig,
You are right. You get prompted because the drilldown and drillthough
interactive features require URL acccess and request goes out on the client
side of the application.
In a nutshell, if your reports have interactive features you need to go for
URL access. For Internet-oriented apps this means writing a custom security
extension. It is not that involved to write and I have deployed an
application that uses a custom security extension in a production
environment. There are some gotchas to avoid but in general my experience
writing custom security extensions have been positive and you will learn a
lot about how RS handles authentication and authorization.
--
Hope this helps.
---
Teo Lachev, MCSD, MCT
Author: "Microsoft Reporting Services in Action"
http://www.prologika.com
"Gash" <Gash@.discussions.microsoft.com> wrote in message
news:FFF038F5-4A21-4DFA-846C-6A3A84683D2D@.microsoft.com...
> Just a thought: Have you tried to setup a individual Application pool that
works with your RSUser Account?
> "Craig HB" wrote:
> > I am building an asp.net app that will use reporting services to show
reports within the application. Users login to the application and when they
need to see a report I use web services to render the report. The asp.net
app and reporting services are on the same windows 2003 server (not using
active directory).
> >
> > Because reporting services uses Windows authentication and does not
allow anonymous access, I have created a windows account (called "RSUser")
that has access to my reports. When the user runs a report, I pass in the
credentials for this windows account like this...
> >
> > rs.Credentials = New System.Net.NetworkCredential("RSUser", "password",
"domain")
> >
> > This all works, and the report renders using the permissions from
RSUser. The problem is that all the reports use treeviews for drill-down
(and some use drill-through). When you expand a drill down you are prompted
for a windows login. I think this is because this postback is now coming
from the client PC, instead if from the asp.net app (i.e. on the server),
and so reporting services needs to anthenticate this new user.
> >
> > The only solution that I have found for this is developing a security
extension for reporting services...
> >
> >
http://msdn.microsoft.com/library/?url=/library/en-us/dnsql2k/html/ufairs.asp?frame=true#ufairs_topic3
> >
> > ... but this seems like overkill and a very complicated process, and
Microsoft says in the article that this is not fully tested and should not
be used in a production environment (but that where I need it for).
> >
> > Does anyone have a solution ?
> >
> > Craig HB|||Start here
http://msdn.microsoft.com/library/?url=/library/en-us/dnsql2k/html/ufairs.asp?frame=true#ufairs_topic3
--
Hope this helps.
----
Teo Lachev, MCSD, MCT
Author: "Microsoft Reporting Services in Action"
Publisher website: http://www.manning.com/lachev
Buy it from Amazon.com: http://shrinkster.com/eq
Home page and blog: http://www.prologika.com/
----
"jbmeeh" <jbmeeh@.discussions.microsoft.com> wrote in message
news:3A2F7D63-C267-4CED-A5CC-4B42186B98B6@.microsoft.com...
> Is there any sample code for writing a custom security extension? I have
> already validated the user and I want to provide url access to the report
> server.
> "Teo" wrote:
> > Craig,
> >
> > You are right. You get prompted because the drilldown and drillthough
> > interactive features require URL acccess and request goes out on the
client
> > side of the application.
> >
> > In a nutshell, if your reports have interactive features you need to go
for
> > URL access. For Internet-oriented apps this means writing a custom
security
> > extension. It is not that involved to write and I have deployed an
> > application that uses a custom security extension in a production
> > environment. There are some gotchas to avoid but in general my
experience
> > writing custom security extensions have been positive and you will learn
a
> > lot about how RS handles authentication and authorization.
> >
> > --
> > Hope this helps.
> >
> > ---
> > Teo Lachev, MCSD, MCT
> > Author: "Microsoft Reporting Services in Action"
> > http://www.prologika.com
> >
> >
> > "Gash" <Gash@.discussions.microsoft.com> wrote in message
> > news:FFF038F5-4A21-4DFA-846C-6A3A84683D2D@.microsoft.com...
> > > Just a thought: Have you tried to setup a individual Application pool
that
> > works with your RSUser Account?
> > >
> > > "Craig HB" wrote:
> > >
> > > > I am building an asp.net app that will use reporting services to
show
> > reports within the application. Users login to the application and when
they
> > need to see a report I use web services to render the report. The
asp.net
> > app and reporting services are on the same windows 2003 server (not
using
> > active directory).
> > > >
> > > > Because reporting services uses Windows authentication and does not
> > allow anonymous access, I have created a windows account (called
"RSUser")
> > that has access to my reports. When the user runs a report, I pass in
the
> > credentials for this windows account like this...
> > > >
> > > > rs.Credentials = New System.Net.NetworkCredential("RSUser",
"password",
> > "domain")
> > > >
> > > > This all works, and the report renders using the permissions from
> > RSUser. The problem is that all the reports use treeviews for drill-down
> > (and some use drill-through). When you expand a drill down you are
prompted
> > for a windows login. I think this is because this postback is now coming
> > from the client PC, instead if from the asp.net app (i.e. on the
server),
> > and so reporting services needs to anthenticate this new user.
> > > >
> > > > The only solution that I have found for this is developing a
security
> > extension for reporting services...
> > > >
> > > >
> >
http://msdn.microsoft.com/library/?url=/library/en-us/dnsql2k/html/ufairs.asp?frame=true#ufairs_topic3
> > > >
> > > > ... but this seems like overkill and a very complicated process, and
> > Microsoft says in the article that this is not fully tested and should
not
> > be used in a production environment (but that where I need it for).
> > > >
> > > > Does anyone have a solution ?
> > > >
> > > > Craig HB
> >
> >
> >|||I have seen this article and it is good if I wanted to build a standalone
application to allow access to the report server. However, i have an existing
application with forms authentication in which I want to embed url access to
the report server. I was hoping that there would be code samples or an
article for this particular issue. I don't need to present another form to
the user to capture credentials. Can i use my existing forms authentication
ticket or do I need to create a new one. Do I call the LogonUser webservice
to create a cookie for a user that has been created on the report manager. It
seems like there are a lot of people trying to solve the same problem, but
not too many examples.
"Teo Lachev" wrote:
> Start here
> http://msdn.microsoft.com/library/?url=/library/en-us/dnsql2k/html/ufairs.asp?frame=true#ufairs_topic3
> --
> Hope this helps.
> ----
> Teo Lachev, MCSD, MCT
> Author: "Microsoft Reporting Services in Action"
> Publisher website: http://www.manning.com/lachev
> Buy it from Amazon.com: http://shrinkster.com/eq
> Home page and blog: http://www.prologika.com/
> ----
> "jbmeeh" <jbmeeh@.discussions.microsoft.com> wrote in message
> news:3A2F7D63-C267-4CED-A5CC-4B42186B98B6@.microsoft.com...
> > Is there any sample code for writing a custom security extension? I have
> > already validated the user and I want to provide url access to the report
> > server.
> >
> > "Teo" wrote:
> >
> > > Craig,
> > >
> > > You are right. You get prompted because the drilldown and drillthough
> > > interactive features require URL acccess and request goes out on the
> client
> > > side of the application.
> > >
> > > In a nutshell, if your reports have interactive features you need to go
> for
> > > URL access. For Internet-oriented apps this means writing a custom
> security
> > > extension. It is not that involved to write and I have deployed an
> > > application that uses a custom security extension in a production
> > > environment. There are some gotchas to avoid but in general my
> experience
> > > writing custom security extensions have been positive and you will learn
> a
> > > lot about how RS handles authentication and authorization.
> > >
> > > --
> > > Hope this helps.
> > >
> > > ---
> > > Teo Lachev, MCSD, MCT
> > > Author: "Microsoft Reporting Services in Action"
> > > http://www.prologika.com
> > >
> > >
> > > "Gash" <Gash@.discussions.microsoft.com> wrote in message
> > > news:FFF038F5-4A21-4DFA-846C-6A3A84683D2D@.microsoft.com...
> > > > Just a thought: Have you tried to setup a individual Application pool
> that
> > > works with your RSUser Account?
> > > >
> > > > "Craig HB" wrote:
> > > >
> > > > > I am building an asp.net app that will use reporting services to
> show
> > > reports within the application. Users login to the application and when
> they
> > > need to see a report I use web services to render the report. The
> asp.net
> > > app and reporting services are on the same windows 2003 server (not
> using
> > > active directory).
> > > > >
> > > > > Because reporting services uses Windows authentication and does not
> > > allow anonymous access, I have created a windows account (called
> "RSUser")
> > > that has access to my reports. When the user runs a report, I pass in
> the
> > > credentials for this windows account like this...
> > > > >
> > > > > rs.Credentials = New System.Net.NetworkCredential("RSUser",
> "password",
> > > "domain")
> > > > >
> > > > > This all works, and the report renders using the permissions from
> > > RSUser. The problem is that all the reports use treeviews for drill-down
> > > (and some use drill-through). When you expand a drill down you are
> prompted
> > > for a windows login. I think this is because this postback is now coming
> > > from the client PC, instead if from the asp.net app (i.e. on the
> server),
> > > and so reporting services needs to anthenticate this new user.
> > > > >
> > > > > The only solution that I have found for this is developing a
> security
> > > extension for reporting services...
> > > > >
> > > > >
> > >
> http://msdn.microsoft.com/library/?url=/library/en-us/dnsql2k/html/ufairs.asp?frame=true#ufairs_topic3
> > > > >
> > > > > ... but this seems like overkill and a very complicated process, and
> > > Microsoft says in the article that this is not fully tested and should
> not
> > > be used in a production environment (but that where I need it for).
> > > > >
> > > > > Does anyone have a solution ?
> > > > >
> > > > > Craig HB
> > >
> > >
> > >
>
>|||> Can i use my existing forms authentication
> ticket or do I need to create a new one.
No, you cannot use your app Forms Authentication ticket and you don't have
to have another logon form. Instead, your application needs to call the RS
LogonUser SOAP API once it authenticates the user. You will end up with two
authentication tickets (cookies) but this shouldn't be too much of an issue.
The MS article should be good enough to address you scenario. You just need
to understand how RS Forms Authentication works by debugging the extension.
I have a two-part article in the works for a magazine about Forms
Authentication. Unfortunately, judging by the editors speed, it won't make
it before the end of the year. Meanwhile, you can check the other threads
on this topic. It's been discussed many times.
--
Hope this helps.
----
Teo Lachev, MCSD, MCT
Author: "Microsoft Reporting Services in Action"
Publisher website: http://www.manning.com/lachev
Buy it from Amazon.com: http://shrinkster.com/eq
Home page and blog: http://www.prologika.com/
----
"jbmeeh" <jbmeeh@.discussions.microsoft.com> wrote in message
news:3F687097-1790-4FF9-B8CB-0A163BF3074C@.microsoft.com...
> I have seen this article and it is good if I wanted to build a standalone
> application to allow access to the report server. However, i have an
existing
> application with forms authentication in which I want to embed url access
to
> the report server. I was hoping that there would be code samples or an
> article for this particular issue. I don't need to present another form to
> the user to capture credentials. Can i use my existing forms
authentication
> ticket or do I need to create a new one. Do I call the LogonUser
webservice
> to create a cookie for a user that has been created on the report manager.
It
> seems like there are a lot of people trying to solve the same problem, but
> not too many examples.
> "Teo Lachev" wrote:
> > Start here
> >
http://msdn.microsoft.com/library/?url=/library/en-us/dnsql2k/html/ufairs.asp?frame=true#ufairs_topic3
> >
> > --
> > Hope this helps.
> >
> > ----
> > Teo Lachev, MCSD, MCT
> > Author: "Microsoft Reporting Services in Action"
> > Publisher website: http://www.manning.com/lachev
> > Buy it from Amazon.com: http://shrinkster.com/eq
> > Home page and blog: http://www.prologika.com/
> > ----
> >
> > "jbmeeh" <jbmeeh@.discussions.microsoft.com> wrote in message
> > news:3A2F7D63-C267-4CED-A5CC-4B42186B98B6@.microsoft.com...
> > > Is there any sample code for writing a custom security extension? I
have
> > > already validated the user and I want to provide url access to the
report
> > > server.
> > >
> > > "Teo" wrote:
> > >
> > > > Craig,
> > > >
> > > > You are right. You get prompted because the drilldown and
drillthough
> > > > interactive features require URL acccess and request goes out on the
> > client
> > > > side of the application.
> > > >
> > > > In a nutshell, if your reports have interactive features you need to
go
> > for
> > > > URL access. For Internet-oriented apps this means writing a custom
> > security
> > > > extension. It is not that involved to write and I have deployed an
> > > > application that uses a custom security extension in a production
> > > > environment. There are some gotchas to avoid but in general my
> > experience
> > > > writing custom security extensions have been positive and you will
learn
> > a
> > > > lot about how RS handles authentication and authorization.
> > > >
> > > > --
> > > > Hope this helps.
> > > >
> > > > ---
> > > > Teo Lachev, MCSD, MCT
> > > > Author: "Microsoft Reporting Services in Action"
> > > > http://www.prologika.com
> > > >
> > > >
> > > > "Gash" <Gash@.discussions.microsoft.com> wrote in message
> > > > news:FFF038F5-4A21-4DFA-846C-6A3A84683D2D@.microsoft.com...
> > > > > Just a thought: Have you tried to setup a individual Application
pool
> > that
> > > > works with your RSUser Account?
> > > > >
> > > > > "Craig HB" wrote:
> > > > >
> > > > > > I am building an asp.net app that will use reporting services to
> > show
> > > > reports within the application. Users login to the application and
when
> > they
> > > > need to see a report I use web services to render the report. The
> > asp.net
> > > > app and reporting services are on the same windows 2003 server (not
> > using
> > > > active directory).
> > > > > >
> > > > > > Because reporting services uses Windows authentication and does
not
> > > > allow anonymous access, I have created a windows account (called
> > "RSUser")
> > > > that has access to my reports. When the user runs a report, I pass
in
> > the
> > > > credentials for this windows account like this...
> > > > > >
> > > > > > rs.Credentials = New System.Net.NetworkCredential("RSUser",
> > "password",
> > > > "domain")
> > > > > >
> > > > > > This all works, and the report renders using the permissions
from
> > > > RSUser. The problem is that all the reports use treeviews for
drill-down
> > > > (and some use drill-through). When you expand a drill down you are
> > prompted
> > > > for a windows login. I think this is because this postback is now
coming
> > > > from the client PC, instead if from the asp.net app (i.e. on the
> > server),
> > > > and so reporting services needs to anthenticate this new user.
> > > > > >
> > > > > > The only solution that I have found for this is developing a
> > security
> > > > extension for reporting services...
> > > > > >
> > > > > >
> > > >
> >
http://msdn.microsoft.com/library/?url=/library/en-us/dnsql2k/html/ufairs.asp?frame=true#ufairs_topic3
> > > > > >
> > > > > > ... but this seems like overkill and a very complicated process,
and
> > > > Microsoft says in the article that this is not fully tested and
should
> > not
> > > > be used in a production environment (but that where I need it for).
> > > > > >
> > > > > > Does anyone have a solution ?
> > > > > >
> > > > > > Craig HB
> > > >
> > > >
> > > >
> >
> >
> >|||Is there any sample code for writing a custom security extension? I have
already validated the user and I want to provide url access to the report
server.
"Teo" wrote:
> Craig,
> You are right. You get prompted because the drilldown and drillthough
> interactive features require URL acccess and request goes out on the client
> side of the application.
> In a nutshell, if your reports have interactive features you need to go for
> URL access. For Internet-oriented apps this means writing a custom security
> extension. It is not that involved to write and I have deployed an
> application that uses a custom security extension in a production
> environment. There are some gotchas to avoid but in general my experience
> writing custom security extensions have been positive and you will learn a
> lot about how RS handles authentication and authorization.
> --
> Hope this helps.
> ---
> Teo Lachev, MCSD, MCT
> Author: "Microsoft Reporting Services in Action"
> http://www.prologika.com
>
> "Gash" <Gash@.discussions.microsoft.com> wrote in message
> news:FFF038F5-4A21-4DFA-846C-6A3A84683D2D@.microsoft.com...
> > Just a thought: Have you tried to setup a individual Application pool that
> works with your RSUser Account?
> >
> > "Craig HB" wrote:
> >
> > > I am building an asp.net app that will use reporting services to show
> reports within the application. Users login to the application and when they
> need to see a report I use web services to render the report. The asp.net
> app and reporting services are on the same windows 2003 server (not using
> active directory).
> > >
> > > Because reporting services uses Windows authentication and does not
> allow anonymous access, I have created a windows account (called "RSUser")
> that has access to my reports. When the user runs a report, I pass in the
> credentials for this windows account like this...
> > >
> > > rs.Credentials = New System.Net.NetworkCredential("RSUser", "password",
> "domain")
> > >
> > > This all works, and the report renders using the permissions from
> RSUser. The problem is that all the reports use treeviews for drill-down
> (and some use drill-through). When you expand a drill down you are prompted
> for a windows login. I think this is because this postback is now coming
> from the client PC, instead if from the asp.net app (i.e. on the server),
> and so reporting services needs to anthenticate this new user.
> > >
> > > The only solution that I have found for this is developing a security
> extension for reporting services...
> > >
> > >
> http://msdn.microsoft.com/library/?url=/library/en-us/dnsql2k/html/ufairs.asp?frame=true#ufairs_topic3
> > >
> > > ... but this seems like overkill and a very complicated process, and
> Microsoft says in the article that this is not fully tested and should not
> be used in a production environment (but that where I need it for).
> > >
> > > Does anyone have a solution ?
> > >
> > > Craig HB
>
>|||Teo. Is it possible to use web forms authentication with the standard
edition of RS?
If not, I'm guessing there is no other way to use the viewer over the
Internet..
Thanks, AHH
BTW: I bought your book - best one out there..|||Thanks. No, extending RS requires Enterprise Edition. Sorry.
How about generating reports on the server side of the app and sacrificing
the interactive features and the toolbar?
--
Hope this helps.
----
Teo Lachev, MCSD, MCT
Author: "Microsoft Reporting Services in Action"
Publisher website: http://www.manning.com/lachev
Buy it from Amazon.com: http://shrinkster.com/eq
Home page and blog: http://www.prologika.com/
----
"AHH" <AHH@.discussions.microsoft.com> wrote in message
news:13CACEA1-BD84-4D6A-BB25-63D43E0F56A8@.microsoft.com...
> Teo. Is it possible to use web forms authentication with the standard
> edition of RS?
> If not, I'm guessing there is no other way to use the viewer over the
> Internet..
> Thanks, AHH
> BTW: I bought your book - best one out there..

Authentication Mode problems

<P

Hi Everyone

I changed the authentication mode from SQL Server Authentication into Windows of my application (Web application - ASP.NET 1.1, SQL Server 2000, C#). I changed the SqlServer into Windows only mode authentication.

The SQL server now contains all the "windows users" as users of the database (Logins as well as Users).

But I faced the problem of 'login failed for NT Authority\Network Service'. So I added the Network Service as user. The login problem is disappeared, but when I'm tracing through the SQL Profiler I can see only the Network Service as user and not my windows username\SQL server login name.

what should I do for displaying the windows username\Sql login name in the profiler instead of "Network Service". Please help me.

Thanks in advance

Fraijo

<P>

The network service is showing up, because the application is running using those credentials not user credentials. If you want it to use user as account, then you need to login as those users which is only possible if they have permisson on the resource.

|||

I'm sorry

it is not solved.. Because I checked all the user rights of my login. is there any other possible way to find the user credentials are suitable for running my application? Please share.

thanks

|||

Impersonation is one way but then you are back to the same issue.

|||

Hi fraijo,

Sorry that i'm a little confused after reading your question. It seems to me that you are able to access to the database successfully after you added the NETWORK SERVER as one database user. Why do you want to use windows username\SQL server login name instead of network service account?

My suggestion could be wrong.But as far as i know, NETWORK SERVICE is the default winodws account used by ASP.NET to access to the database,so, you cannot change this account to another one.

Hope my suggestion could help

|||

Hi

As you said I can access the database. But in my application it is checking authorization by taking the windows usernames. So I need the windows username as users for the SQL Server too..

thanks in advance

Fraijo

Authentication methods for connections to SQL Server in ASP Pages

I am trying to access SQL Server via an ASP page using a Trusted Connection,
but it is not working. When I run the page I receive the following error
message:
Microsoft OLE DB Service Components error '80040e21'
Multiple-step OLE DB operation generated errors. Check each OLE DB status
value, if available. No work was done.
line 35
My connection string is in a separate file: cst = "data
source=X099789\Widgets;Initial Catalog=Automotive; Integrated Security=SSPI;
"
My code snipet looks like the following:
set OBJRST = Server.CreateObject("ADODB.Recordset")
Set objComm = Server.CreateObject("ADODB.Command")
objComm.ActiveConnection = cst '****LIne 35
MotorsSQL = "usp_MotorAll"
UIPWSQL = "usp_UIPW '" & struserid &"', '"& strpassword &"';"
objConnAll.open cst
What is the correct coding to connect to the SQL Server using Windows
Authentication in an ASP page?
I have read the instructions on:
http://support.microsoft.com/default.aspx/kb/247931, made the changes,
however the page still will not work.
Kindly assist. I will be thankful.
AuntieAuntieAuntie> Microsoft OLE DB Service Components error '80040e21'
> Multiple-step OLE DB operation generated errors. Check each OLE DB status
> value, if available. No work was done.
These errors are probably because there is no 'Provider' keyword in your
OLEDB connection string. Try adding 'Provider=SQLOLEDB'.
Hope this helps.
Dan Guzman
SQL Server MVP
"AuntieAuntieAuntie" <AuntieAuntieAuntie@.discussions.microsoft.com> wrote in
message news:EE4C5259-1F64-4893-9AF1-657A1C03917B@.microsoft.com...
>I am trying to access SQL Server via an ASP page using a Trusted
>Connection,
> but it is not working. When I run the page I receive the following error
> message:
> Microsoft OLE DB Service Components error '80040e21'
> Multiple-step OLE DB operation generated errors. Check each OLE DB status
> value, if available. No work was done.
> line 35
> My connection string is in a separate file: cst = "data
> source=X099789\Widgets;Initial Catalog=Automotive; Integrated
> Security=SSPI;"
> My code snipet looks like the following:
> set OBJRST = Server.CreateObject("ADODB.Recordset")
> Set objComm = Server.CreateObject("ADODB.Command")
> objComm.ActiveConnection = cst '****LIne 35
> MotorsSQL = "usp_MotorAll"
> UIPWSQL = "usp_UIPW '" & struserid &"', '"& strpassword &"';"
> objConnAll.open cst
> What is the correct coding to connect to the SQL Server using Windows
> Authentication in an ASP page?
> I have read the instructions on:
> http://support.microsoft.com/default.aspx/kb/247931, made the changes,
> however the page still will not work.
> Kindly assist. I will be thankful.
> AuntieAuntieAuntie
>

Authentication in SQL Server

Hi folks,

Got a massive problem.
I'm starting to develop a new website using ASP.NET v3.5 (the CTP of Microsoft's new Visual Web Developer "Orcas"). I saved the project onto a network drive (called X: for the purposes of this discussion) - X:\Orcas_WS\VVProject.
Set up a new database and table to upload files from an ASP.NET AJAX form to a database table, fields as follows:
[ImgID] - type int, Identity field, auto-increment of 1;
[GallID] - type int;
[GallPos] - type int;
[ImgContentType] - type nchar(10);
[ImgContent] - type image (binary data);

Worked a dream when running the development server on my laptop and using my own username and password to upload stuff to (in connection string, using Integrated Security = SSPI).
Have now uploaded this to a live server, installed .NET Framework v3.5 on my server, (attempted to) enable IUSR_MACHINE account to have read/write access to my DB and now it's all gone to pot. None of my accounts can authenticate against this DB using .NET - whether it's NETWORK SERVICE, or IUSR_CM-SUMM, even my own username and password, with Integrated Security both set to SSPI and False.
I am now getting intermittent errors where login will either succeed but .NET will try and use named pipes and says "there is nothing on the other end of the pipe" with an event 3005 in my event log, or I get an "login failed for user (username)" message and a failure audit in my event log.

I read up somewhere about having to deregister the Service Principal Name to avoid auth errors (which is what seems to be happening), but having trawled Microsoft's website for hours, there seems to be no documentation or instruction on how to deregister an SPN.

Please help folks, this project is financially lucrative and I want to make a fantastic impression on the client with a low development time overhead!

Many thanks in advance,

medicineworkerAdmin, please close thread, problem sorted - my MS SQL Server was being stoopid, my schema permissions had (rather strangely) been entirely wiped out... lol, have fixed.

Cheers!

authentication error

I've been having a problem connecting to my sql 2000 server database.
The error reads...
Server Error in '/ASP' Application.
Login failed for user 'NT AUTHORITY\NETWORK SERVICE'.
Description: An unhandled exception occurred during the execution of
the current web request. Please review the stack trace for more
information about the error and where it originated in the code.
Exception Details: System.Data.SqlClient.SqlException: Login failed for
user 'NT AUTHORITY\NETWORK SERVICE'.
Source Error:
Line 30: //{
Line 31: Response.Write("before the open");
Line 32: objConn.Open();
Line 33: //SQLConnection.Open();
Line 34: //SqlCommand objCmd = new SqlCommand(sql,
objConn);
My web.config file reads:
<configuration>
<appSettings>
<add key="Andrew" value="Data Source=localhost;Initial
Catalog=Andrew;User Id=xxxx;Password=xxxxxxxxx"/>
</appSettings>
<system.web>
<compilation defaultLanguage="C#" debug="true" />
<customErrors mode="Off" />
<globalization requestEncoding="UTF-8" responseEncoding="UTF-8" />
</system.web>
</configuration>
My connection string in my C# file reads...
protected SqlConnection objConn = new
SqlConnection(System.Configuration.ConfigurationSe ttings.AppSettings["Andrew"]);
What I've done so far:
Under security in SQL server I've set up an account with the ID. It
shows type as Standard, Server access as Permit and Default Database as
Andrew.
Under my SQL Server Login Properties I've set the Authentication to
"SQL Server Authentication" and entered the password.
Under the defaults menu I've set the database to Andrew.
Under server roles "System Admins" is checked.
Under the Database Access tab my user ID is assigned to the
"Andrew" database with a checkmark in the permit box.
Database roles for Andrew are set to public and db_owner.
I can't seem to find my way around this error. Any help would be
greatly appreciated!
Thanks,
Andrew
Andrew,
When using ASP.NET the default security contect is the service accoutn that
IIS is running under. This is called using a trusted conneciton. By default
this account is not given access to SQL Server; to resolve this you should
give the correct permissions to SQL Server so the account that IIS is
running under has permissions to access SQL. Your connection is currently
connecting in this mode.
Adding "Integrated Security=false" to your connection string will turn this
feature off (then you should be good to go).
If you are inclined to use Integrated Authentication you should
1. Add Integrated Security=SSPI (and remove the user and password from your
conneciton string)
2. <system.web>
<authentication mode = "windows" />
<identity impersonate="true" />
</system.web>
3. Then open the IIS Admin tool and go to the properties window of where
your application is running. You then choose the Directory Security tab and
uncheck Anonymous & provide the username and password for the user. This
changes the context of the applicaiton to run under your user.
4. Grant the appropriate privilages to the user.
Brad Sarsfield [MSFT] bradsa(at)microsoft.com
This posting is provided "AS IS", with no warranties, and confers no rights.
<andrew.grande@.gmail.com> wrote in message
news:1127878854.885819.309560@.g49g2000cwa.googlegr oups.com...
> I've been having a problem connecting to my sql 2000 server database.
> The error reads...
> Server Error in '/ASP' Application.
> Login failed for user 'NT AUTHORITY\NETWORK SERVICE'.
> Description: An unhandled exception occurred during the execution of
> the current web request. Please review the stack trace for more
> information about the error and where it originated in the code.
> Exception Details: System.Data.SqlClient.SqlException: Login failed for
> user 'NT AUTHORITY\NETWORK SERVICE'.
> Source Error:
> Line 30: //{
> Line 31: Response.Write("before the open");
> Line 32: objConn.Open();
> Line 33: //SQLConnection.Open();
> Line 34: //SqlCommand objCmd = new SqlCommand(sql,
> objConn);
>
> My web.config file reads:
> <configuration>
> <appSettings>
> <add key="Andrew" value="Data Source=localhost;Initial
> Catalog=Andrew;User Id=xxxx;Password=xxxxxxxxx"/>
> </appSettings>
> <system.web>
> <compilation defaultLanguage="C#" debug="true" />
> <customErrors mode="Off" />
> <globalization requestEncoding="UTF-8" responseEncoding="UTF-8" />
> </system.web>
> </configuration>
> My connection string in my C# file reads...
> protected SqlConnection objConn = new
> SqlConnection(System.Configuration.ConfigurationSe ttings.AppSettings["Andrew"]);
> What I've done so far:
> Under security in SQL server I've set up an account with the ID. It
> shows type as Standard, Server access as Permit and Default Database as
> Andrew.
> Under my SQL Server Login Properties I've set the Authentication to
> "SQL Server Authentication" and entered the password.
> Under the defaults menu I've set the database to Andrew.
> Under server roles "System Admins" is checked.
> Under the Database Access tab my user ID is assigned to the
> "Andrew" database with a checkmark in the permit box.
> Database roles for Andrew are set to public and db_owner.
> I can't seem to find my way around this error. Any help would be
> greatly appreciated!
> Thanks,
> Andrew
>

authentication error

I've been having a problem connecting to my sql 2000 server database.
The error reads...
Server Error in '/ASP' Application.
Login failed for user 'NT AUTHORITY\NETWORK SERVICE'.
Description: An unhandled exception occurred during the execution of
the current web request. Please review the stack trace for more
information about the error and where it originated in the code.
Exception Details: System.Data.SqlClient.SqlException: Login failed for
user 'NT AUTHORITY\NETWORK SERVICE'.
Source Error:
Line 30: //{
Line 31: Response.Write("before the open");
Line 32: objConn.Open();
Line 33: //SQLConnection.Open();
Line 34: //SqlCommand objCmd = new SqlCommand(sql,
objConn);
My web.config file reads:
<configuration>
<appSettings>
<add key="Andrew" value="Data Source=localhost;Initial
Catalog=Andrew;User Id=xxxx;Password=xxxxxxxxx"/>
</appSettings>
<system.web>
<compilation defaultLanguage="C#" debug="true" />
<customErrors mode="Off" />
<globalization requestEncoding="UTF-8" responseEncoding="UTF-8" />
</system.web>
</configuration>
My connection string in my C# file reads...
protected SqlConnection objConn = new
SqlConnection(System.Configuration.ConfigurationSettings.AppSettings["An
drew"]);
What I've done so far:
Under security in SQL server I've set up an account with the ID. It
shows type as Standard, Server access as Permit and Default Database as
Andrew.
Under my SQL Server Login Properties I've set the Authentication to
"SQL Server Authentication" and entered the password.
Under the defaults menu I've set the database to Andrew.
Under server roles "System Admins" is checked.
Under the Database Access tab my user ID is assigned to the
"Andrew" database with a checkmark in the permit box.
Database roles for Andrew are set to public and db_owner.
I can't seem to find my way around this error. Any help would be
greatly appreciated!
Thanks,
AndrewAndrew,
When using ASP.NET the default security contect is the service accoutn that
IIS is running under. This is called using a trusted conneciton. By default
this account is not given access to SQL Server; to resolve this you should
give the correct permissions to SQL Server so the account that IIS is
running under has permissions to access SQL. Your connection is currently
connecting in this mode.
Adding "Integrated Security=false" to your connection string will turn this
feature off (then you should be good to go).
If you are inclined to use Integrated Authentication you should
1. Add Integrated Security=SSPI (and remove the user and password from your
conneciton string)
2. <system.web>
<authentication mode = "windows" />
<identity impersonate="true" />
</system.web>
3. Then open the IIS Admin tool and go to the properties window of where
your application is running. You then choose the Directory Security tab and
uncheck Anonymous & provide the username and password for the user. This
changes the context of the applicaiton to run under your user.
4. Grant the appropriate privilages to the user.
Brad Sarsfield [MSFT] bradsa(at)microsoft.com
This posting is provided "AS IS", with no warranties, and confers no rights.
<andrew.grande@.gmail.com> wrote in message
news:1127878854.885819.309560@.g49g2000cwa.googlegroups.com...
> I've been having a problem connecting to my sql 2000 server database.
> The error reads...
> Server Error in '/ASP' Application.
> Login failed for user 'NT AUTHORITY\NETWORK SERVICE'.
> Description: An unhandled exception occurred during the execution of
> the current web request. Please review the stack trace for more
> information about the error and where it originated in the code.
> Exception Details: System.Data.SqlClient.SqlException: Login failed for
> user 'NT AUTHORITY\NETWORK SERVICE'.
> Source Error:
> Line 30: //{
> Line 31: Response.Write("before the open");
> Line 32: objConn.Open();
> Line 33: //SQLConnection.Open();
> Line 34: //SqlCommand objCmd = new SqlCommand(sql,
> objConn);
>
> My web.config file reads:
> <configuration>
> <appSettings>
> <add key="Andrew" value="Data Source=localhost;Initial
> Catalog=Andrew;User Id=xxxx;Password=xxxxxxxxx"/>
> </appSettings>
> <system.web>
> <compilation defaultLanguage="C#" debug="true" />
> <customErrors mode="Off" />
> <globalization requestEncoding="UTF-8" responseEncoding="UTF-8" />
> </system.web>
> </configuration>
> My connection string in my C# file reads...
> protected SqlConnection objConn = new
> SqlConnection(System.Configuration.ConfigurationSettings.AppSettings["
Andrew"]);
> What I've done so far:
> Under security in SQL server I've set up an account with the ID. It
> shows type as Standard, Server access as Permit and Default Database as
> Andrew.
> Under my SQL Server Login Properties I've set the Authentication to
> "SQL Server Authentication" and entered the password.
> Under the defaults menu I've set the database to Andrew.
> Under server roles "System Admins" is checked.
> Under the Database Access tab my user ID is assigned to the
> "Andrew" database with a checkmark in the permit box.
> Database roles for Andrew are set to public and db_owner.
> I can't seem to find my way around this error. Any help would be
> greatly appreciated!
> Thanks,
> Andrew
>

Authentication and authorization in MS Reports 2000

Hi,
I am Strug'ling from 1 week. Please help me with following problem.
1. I am developing ASP.net, C# web application.
2. I am using MS Reporting Services 2000 for Report Generation.
3. I am using Report Viewer Control for Displaying the Reports into my web
application.
4. I am able to see my all reports in report viewer control but for that
either i have to have Anonymous access true in IIS Or I have to add that user
to Report Server.
My problem :-
I don't want to use Anonymous access, because then it will become public.
I use form authentication in My application and based on this i want to
validate the report to be seen to user.
So how do i do this.
Please help me.
Thanks in advance.
Labhesh Shrimali
BangaloreReporting services by default using Windows authentication as you have
discovered. If you want to use forms authentication you have to use the
security extensions which allow you to authenticate the users rather than
Reporting Services. Here is a link to start off with.
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql2k/html/ufairs.asp
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Labhesh Shrimali - Bangalore"
<LabheshShrimaliBangalore@.discussions.microsoft.com> wrote in message
news:B2053588-9CAF-4FA0-9F65-1B5621FAD01A@.microsoft.com...
> Hi,
> I am Strug'ling from 1 week. Please help me with following problem.
> 1. I am developing ASP.net, C# web application.
> 2. I am using MS Reporting Services 2000 for Report Generation.
> 3. I am using Report Viewer Control for Displaying the Reports into my web
> application.
> 4. I am able to see my all reports in report viewer control but for that
> either i have to have Anonymous access true in IIS Or I have to add that
> user
> to Report Server.
> My problem :-
> I don't want to use Anonymous access, because then it will become public.
> I use form authentication in My application and based on this i want to
> validate the report to be seen to user.
> So how do i do this.
> Please help me.
> Thanks in advance.
> Labhesh Shrimali
> Bangalore
>sql

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 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.

Auth.

what if we are making the ASP(Application Service provider) model so we have
to go for windows authentication or standard authentication?
Can you be more elaborate on what your question is?
You can use either of the mechanism as authentication models. But what is
your requirement?
HTH,
Vinod Kumar
MCSE, DBA, MCAD, MCSD
http://www.extremeexperts.com
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
"Rogers" <Rogers@.discussions.microsoft.com> wrote in message
news:F11D42D4-EA39-4E22-A390-DB7A933FD91B@.microsoft.com...
> what if we are making the ASP(Application Service provider) model so we
have
> to go for windows authentication or standard authentication?
|||at what point we have to go for windows authentication and at what point we
have to consider to go for standard authentication means through database
user.
"Vinod Kumar" wrote:

> Can you be more elaborate on what your question is?
> You can use either of the mechanism as authentication models. But what is
> your requirement?
> --
> HTH,
> Vinod Kumar
> MCSE, DBA, MCAD, MCSD
> http://www.extremeexperts.com
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techinf...2000/books.asp
> "Rogers" <Rogers@.discussions.microsoft.com> wrote in message
> news:F11D42D4-EA39-4E22-A390-DB7A933FD91B@.microsoft.com...
> have
>
>

Auditing SQL Server 2005 through transaction log

Hello,

We are maintaining an internal ASP.NET v2.0 website which is quite big and already in production. The underlying SQL Server 2005 database contains 350+ tables.

Recently, we have been asked to implement a new feature which seems functionally quite simple. We have to track every single data modification, which includes insertions, deletions and modifications. This information should be presented to power users in the form of readable strings right in an admin section of our website.

Our team of architects is working on a way to make it possible without putting the SQL Server to a crawl. One thing is for sure, SQL Server 2005 already does the job through its transaction log. It should be a good idea to use it directly instead of managing our own log based on triggers. Why put more pressure on the server to write data that is already logged by the database engine? We have heard that Microsoft's SQL Server team do not support this concept and are wondering why...

It's quite easy to find queries on the web that output very useful information such as date of transactions and what they have done. Although, the data involved in those transactions seems to be stored in a binary field which can be retrived using this query: SELECT "log record" FROM ::fn_dblog(null,null)

3rd parties such as Apex SQL are already doing a great job at decrypting it for us. This is very useful but not efficient since those tools do a very generic job. We would like to optimize it for our needs. All we need to know is who made the modifications, when, in which tables and what are the new values.

We believe that we would have to decrypt the "log record" field from the ::fn_dblog(null, null) table. Is there any way to get basic documentation about how to do it?

Thanks!

Marc Lacoursiere

RooSoft Computing

The format of the transaction log is undocumented, as it will change from release to release.

Thanks,|||

Hi,

I'm working with Marc on that point and I would like to know if there is or if Microsoft expect to expose an interface that can allow us to read the transaction log. What are the plans for futur releases.

Thanks

|||The transaction log contains physical information that are often just blocks of bytes which are meaningless in terms of the DDL or DML that instigated them. The log was never intended for audit purposes and really should not be used that way.|||

Thanks for your Post Peter,

I understand that the transaction log purpose is for recovery only but you must admit that it could be a great way to audit changes without adding any overhead to the server.

What we would like to do is taking a transaction log backup on a regulary basis and read those files as they cumulated in the directory. This will allow us to log any DML changes into a Log table located on another server.

I also understand that the online transaction log format can change from one version to another but correct me if I'm wrong, the transaction log backup is compatible between SQL 2000 and SQL 2005 that means that the format of a .trn file should remain compatible as SQL Server evoluate.

thanks for your feedback|||

Adding the information needed to use the log for auditing would add significant overhead which is one of the reasons it has not been done.

You might look at the Change Data Capture functionality in the upcoming SQL Server 2008 release. http://connect.microsoft.com/sqlserver/ has a CTP preview release available.

|||

Our guess as of now is that some timely transaction log backup analysis on a remote machine could help keep our database server usage to an acceptable level as it already handles loads of data.

We doubt that using triggers on each table would be much more effective. This would certainly slow down every transaction. As our database grows, we must optimize DML operations to keep the website running fast.

|||

>> SELECT "log record" FROM ::fn_dblog(null,null)

>> All we need to know is who made the modifications, when, in which tables and what are the new values.

"Who" is not available on a log record by log record basis. It is not part of that binary data in most cases. There are some few records that contain a "who" such as BEGIN TRAN log records. This is included in other output columns of fn_dblog().

When is not available on a log record by log record basis. There are some few record types that contain a "when" such as BEGIN TRAN and END TRAN log records. These are included as other columns in fn_dblog().

Which table is not actually stored in the log record in SQL 2005 and later. This is due to partitioning. The partition has to be linked through the catalog metadata back to its base table and index. This is attempted by fn_dblog() itself and shows up as other columns in the output. DDL can make this lookup fail.

"New values" are often just a byte by byte binary diff of the old row from the new row, not the full values themselves. The log record code does not actually know how to crack the row binary data, it just passes it along to other components in the system. i.e., if you update a INT column from a value of 1 to a value of 257, we may only log a single byte 0x01 as the difference.

The SQL 2005 output for fn_dblog() has been supplemented to try to make some of this easier, but the reality is that some of what you want is just not in the log in many cases.

|||

Thanks Peter for this interesting complement of information.

We actually take care of the "When" and the "Who" because each table of our database contain an updatedBy and updatedDt column. These columns are systematicly updated on each changes by the application layer. We only need to find the new values of the modification.

For the byte by byte binary diff, I guess that you are talking about the online transaction log because taking a closer look at the transaction log backup file (.trn) indicates that new values are stored in the file. I've opened it with an hex editor and I was able to see the new inserted values.

|||

You could look at AuditDB of Lumigent, can be customized on details what you demand.

With best regards.

Jan H. Kanon

Auditing SQL Server 2005 through transaction log

Hello,

We are maintaining an internal ASP.NET v2.0 website which is quite big and already in production. The underlying SQL Server 2005 database contains 350+ tables.

Recently, we have been asked to implement a new feature which seems functionally quite simple. We have to track every single data modification, which includes insertions, deletions and modifications. This information should be presented to power users in the form of readable strings right in an admin section of our website.

Our team of architects is working on a way to make it possible without putting the SQL Server to a crawl. One thing is for sure, SQL Server 2005 already does the job through its transaction log. It should be a good idea to use it directly instead of managing our own log based on triggers. Why put more pressure on the server to write data that is already logged by the database engine? We have heard that Microsoft's SQL Server team do not support this concept and are wondering why...

It's quite easy to find queries on the web that output very useful information such as date of transactions and what they have done. Although, the data involved in those transactions seems to be stored in a binary field which can be retrived using this query: SELECT "log record" FROM ::fn_dblog(null,null)

3rd parties such as Apex SQL are already doing a great job at decrypting it for us. This is very useful but not efficient since those tools do a very generic job. We would like to optimize it for our needs. All we need to know is who made the modifications, when, in which tables and what are the new values.

We believe that we would have to decrypt the "log record" field from the ::fn_dblog(null, null) table. Is there any way to get basic documentation about how to do it?

Thanks!

Marc Lacoursiere

RooSoft Computing

The format of the transaction log is undocumented, as it will change from release to release.

Thanks,|||

Hi,

I'm working with Marc on that point and I would like to know if there is or if Microsoft expect to expose an interface that can allow us to read the transaction log. What are the plans for futur releases.

Thanks

|||The transaction log contains physical information that are often just blocks of bytes which are meaningless in terms of the DDL or DML that instigated them. The log was never intended for audit purposes and really should not be used that way.|||

Thanks for your Post Peter,

I understand that the transaction log purpose is for recovery only but you must admit that it could be a great way to audit changes without adding any overhead to the server.

What we would like to do is taking a transaction log backup on a regulary basis and read those files as they cumulated in the directory. This will allow us to log any DML changes into a Log table located on another server.

I also understand that the online transaction log format can change from one version to another but correct me if I'm wrong, the transaction log backup is compatible between SQL 2000 and SQL 2005 that means that the format of a .trn file should remain compatible as SQL Server evoluate.

thanks for your feedback|||

Adding the information needed to use the log for auditing would add significant overhead which is one of the reasons it has not been done.

You might look at the Change Data Capture functionality in the upcoming SQL Server 2008 release. http://connect.microsoft.com/sqlserver/ has a CTP preview release available.

|||

Our guess as of now is that some timely transaction log backup analysis on a remote machine could help keep our database server usage to an acceptable level as it already handles loads of data.

We doubt that using triggers on each table would be much more effective. This would certainly slow down every transaction. As our database grows, we must optimize DML operations to keep the website running fast.

|||

>> SELECT "log record" FROM ::fn_dblog(null,null)

>> All we need to know is who made the modifications, when, in which tables and what are the new values.

"Who" is not available on a log record by log record basis. It is not part of that binary data in most cases. There are some few records that contain a "who" such as BEGIN TRAN log records. This is included in other output columns of fn_dblog().

When is not available on a log record by log record basis. There are some few record types that contain a "when" such as BEGIN TRAN and END TRAN log records. These are included as other columns in fn_dblog().

Which table is not actually stored in the log record in SQL 2005 and later. This is due to partitioning. The partition has to be linked through the catalog metadata back to its base table and index. This is attempted by fn_dblog() itself and shows up as other columns in the output. DDL can make this lookup fail.

"New values" are often just a byte by byte binary diff of the old row from the new row, not the full values themselves. The log record code does not actually know how to crack the row binary data, it just passes it along to other components in the system. i.e., if you update a INT column from a value of 1 to a value of 257, we may only log a single byte 0x01 as the difference.

The SQL 2005 output for fn_dblog() has been supplemented to try to make some of this easier, but the reality is that some of what you want is just not in the log in many cases.

|||

Thanks Peter for this interesting complement of information.

We actually take care of the "When" and the "Who" because each table of our database contain an updatedBy and updatedDt column. These columns are systematicly updated on each changes by the application layer. We only need to find the new values of the modification.

For the byte by byte binary diff, I guess that you are talking about the online transaction log because taking a closer look at the transaction log backup file (.trn) indicates that new values are stored in the file. I've opened it with an hex editor and I was able to see the new inserted values.

|||

You could look at AuditDB of Lumigent, can be customized on details what you demand.

With best regards.

Jan H. Kanon

Auditing SQL Server 2005 through transaction log

Hello,

We are maintaining an internal ASP.NET v2.0 website which is quite big and already in production. The underlying SQL Server 2005 database contains 350+ tables.

Recently, we have been asked to implement a new feature which seems functionally quite simple. We have to track every single data modification, which includes insertions, deletions and modifications. This information should be presented to power users in the form of readable strings right in an admin section of our website.

Our team of architects is working on a way to make it possible without putting the SQL Server to a crawl. One thing is for sure, SQL Server 2005 already does the job through its transaction log. It should be a good idea to use it directly instead of managing our own log based on triggers. Why put more pressure on the server to write data that is already logged by the database engine? We have heard that Microsoft's SQL Server team do not support this concept and are wondering why...

It's quite easy to find queries on the web that output very useful information such as date of transactions and what they have done. Although, the data involved in those transactions seems to be stored in a binary field which can be retrived using this query: SELECT "log record" FROM ::fn_dblog(null,null)

3rd parties such as Apex SQL are already doing a great job at decrypting it for us. This is very useful but not efficient since those tools do a very generic job. We would like to optimize it for our needs. All we need to know is who made the modifications, when, in which tables and what are the new values.

We believe that we would have to decrypt the "log record" field from the ::fn_dblog(null, null) table. Is there any way to get basic documentation about how to do it?

Thanks!

Marc Lacoursiere

RooSoft Computing

The format of the transaction log is undocumented, as it will change from release to release.

Thanks,|||

Hi,

I'm working with Marc on that point and I would like to know if there is or if Microsoft expect to expose an interface that can allow us to read the transaction log. What are the plans for futur releases.

Thanks

|||The transaction log contains physical information that are often just blocks of bytes which are meaningless in terms of the DDL or DML that instigated them. The log was never intended for audit purposes and really should not be used that way.|||

Thanks for your Post Peter,

I understand that the transaction log purpose is for recovery only but you must admit that it could be a great way to audit changes without adding any overhead to the server.

What we would like to do is taking a transaction log backup on a regulary basis and read those files as they cumulated in the directory. This will allow us to log any DML changes into a Log table located on another server.

I also understand that the online transaction log format can change from one version to another but correct me if I'm wrong, the transaction log backup is compatible between SQL 2000 and SQL 2005 that means that the format of a .trn file should remain compatible as SQL Server evoluate.

thanks for your feedback|||

Adding the information needed to use the log for auditing would add significant overhead which is one of the reasons it has not been done.

You might look at the Change Data Capture functionality in the upcoming SQL Server 2008 release. http://connect.microsoft.com/sqlserver/ has a CTP preview release available.

|||

Our guess as of now is that some timely transaction log backup analysis on a remote machine could help keep our database server usage to an acceptable level as it already handles loads of data.

We doubt that using triggers on each table would be much more effective. This would certainly slow down every transaction. As our database grows, we must optimize DML operations to keep the website running fast.

|||

>> SELECT "log record" FROM ::fn_dblog(null,null)

>> All we need to know is who made the modifications, when, in which tables and what are the new values.

"Who" is not available on a log record by log record basis. It is not part of that binary data in most cases. There are some few records that contain a "who" such as BEGIN TRAN log records. This is included in other output columns of fn_dblog().

When is not available on a log record by log record basis. There are some few record types that contain a "when" such as BEGIN TRAN and END TRAN log records. These are included as other columns in fn_dblog().

Which table is not actually stored in the log record in SQL 2005 and later. This is due to partitioning. The partition has to be linked through the catalog metadata back to its base table and index. This is attempted by fn_dblog() itself and shows up as other columns in the output. DDL can make this lookup fail.

"New values" are often just a byte by byte binary diff of the old row from the new row, not the full values themselves. The log record code does not actually know how to crack the row binary data, it just passes it along to other components in the system. i.e., if you update a INT column from a value of 1 to a value of 257, we may only log a single byte 0x01 as the difference.

The SQL 2005 output for fn_dblog() has been supplemented to try to make some of this easier, but the reality is that some of what you want is just not in the log in many cases.

|||

Thanks Peter for this interesting complement of information.

We actually take care of the "When" and the "Who" because each table of our database contain an updatedBy and updatedDt column. These columns are systematicly updated on each changes by the application layer. We only need to find the new values of the modification.

For the byte by byte binary diff, I guess that you are talking about the online transaction log because taking a closer look at the transaction log backup file (.trn) indicates that new values are stored in the file. I've opened it with an hex editor and I was able to see the new inserted values.

Audit Table

The current environment is an ASP frontend with IIS 5.0
using (windows authentication) and SQL Server 2000(windows
authentication) on the same machine.
I would like to add two columns onto several tables and
have a timestamp and username inserted into them when a
user performs an update or insert. I believe it's a good
idea to use an insert or update trigger, but I'm not sure
how the asp application delegates who is logged to sql
server.
What is the best way to do this?
Do you need to have IIS and Sql server configured a
certain way in order to grab the username from the asp
application?Front-end code typically has to influence on a trigger. The trigger fires
as a result of the triggering action - INSERT, UPDATE or DELETE. Here's an
example to do what you want:
create trigger triu_MyTable on MyTable after insert, update
as
if @.@.ROWCOUNT = 0
return
update MyTable
set
LastModBy = CURRENT_USER
, LastUpdateDateTime = CURRENT_TIMESTAMP
where
PK in (select PK from inserted)
go
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
.
"Michelle" <michelle.vanden@.eglin.af.mil> wrote in message
news:034501c3cdc5$1a2f3a60$a401280a@.phx.gbl...
The current environment is an ASP frontend with IIS 5.0
using (windows authentication) and SQL Server 2000(windows
authentication) on the same machine.
I would like to add two columns onto several tables and
have a timestamp and username inserted into them when a
user performs an update or insert. I believe it's a good
idea to use an insert or update trigger, but I'm not sure
how the asp application delegates who is logged to sql
server.
What is the best way to do this?
Do you need to have IIS and Sql server configured a
certain way in order to grab the username from the asp
application?|||Hi Michelle,
Thanks for your post. According to your description, I understand that you
want to record and return the current login username to certain table in
SQL Server, when you performed insert or update action. If I have
misunderstood, please feel free to let me know.
Before we go any further, I would like to collect more information from
you: 1. Which username do you want to record, the usernames used to log on
IIS or SQL Server?
2. Which authentication do you choose to log on IIS?
So far as I know, if we want to record the usernames used for SQL Server,
we can try to use suser_sname() to return the string of the current login
identification name
For more information regarding suser_sname function, please refer to the
following article on SQL Server Books Online.
Topic: "SUSER_SNAME"
On the SQL Server side, it seems hard to record the usernames which are
used to log on IIS.
Thanks for using MSDN newsgroup.
Regards,
Michael Shao
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.|||1). Both IIS and SQL Server if possible. It would be
better if we get the username from IIS and have it
delegated to SQL Server.
2). Basic Authentication w/SSL on IIS on one machine and
windows Authentication w/SSL on another.

quote:

---

>--Original Message--
>Hi Michelle,
>Thanks for your post. According to your description, I

---

understand that you

quote:

---

>want to record and return the current login username to

---

certain table in

quote:

---

>SQL Server, when you performed insert or update action.

---

If I have

quote:

---

>misunderstood, please feel free to let me know.
>Before we go any further, I would like to collect more

---

information from

quote:

---

>you: 1. Which username do you want to record, the

---

usernames used to log on

quote:

---

>IIS or SQL Server?
>2. Which authentication do you choose to log on IIS?
>So far as I know, if we want to record the usernames used

---

for SQL Server,

quote:

---

>we can try to use suser_sname() to return the string of

---

the current login

quote:

---

>identification name
>For more information regarding suser_sname function,

---

please refer to the

quote:

---

>following article on SQL Server Books Online.
>Topic: "SUSER_SNAME"
>On the SQL Server side, it seems hard to record the

---

usernames which are

quote:

---

>used to log on IIS.
>Thanks for using MSDN newsgroup.
>Regards,
>Michael Shao
>Microsoft Online Partner Support
>Get Secure! - www.microsoft.com/security
>This posting is provided "as is" with no warranties and

---

confers no rights.

quote:

---

>
>.
>

---

|||Hi Michelle,
Thanks for your feedback. In this case, as IIS and SQL Server are on the
same machine, a user's credentials (username:password) will be used to
login to SQL Server after that user has logged into IIS using Basic
authentication.
We are able to record the login information (current login username and
timestamp) for SQL Server using the trigger and the related functions
(SUSER_SNAME, GETDATE() etc.). However, it seems impossible to monitor the
logins to IIS from the SQL Server side. SQL Server is unable to be used to
monitor the logins to IIS.
Please feel free to post in the group if this solves your problem or if you
would like further assistance.
Regards,
Michael Shao
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.|||Hi Michelle,
How is this issue going on your side? Based on my further research, it
seems possible to monitor and record the logins to IIS via ASP programming.
To obtain the detailed information regarding monitoring the logins to IIS
using ASP, it is best that you can post in the ASP newsgroup, such as
microsoft.public.inetserver.asp.general,
microsoft.public.inetserver.asp.db. The ASP newsgroup is primarily for
issues involving ASP programming. The reason why we recommend posting
appropriately is you will get the most qualified pool of respondents, and
other partners who read the newsgroups regularly can either share their
knowledge or learn from your interaction with us.
Thanks for using Microsoft newsgroup.
Regards,
Michael Shao
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.

Attaching files

I have just started using ASP.net.
I have to develop a system that attaches word and PDF files to form. I also need to be able to do a full text search on the documents attached.
I'm currently using SQL server 2000 as my backend DBMS, but I have never used it to attach files. My mates recommend using BLOGS but I'm not sure how to do this and I'm not sure if I can do a full text search using this solution.
Could some body please recommend a solution to get me started.
Thanks in advanced
PaddyYou have two requirements because Word and PDF files are better stored as IMAGE while FULL TEXT uses TEXT and NTEXT. FULL TEXT is an add on to SQL Server dependent on Microsoft Search and the Catalogs must be populate to get search results. Run a search for FULL TEXT, CONTAINS, CONTAINSTABLE, FREETEXT and FREETEXTTABLE in the BOL(books online). Hope this helps.

attaching a DB in express 2005

I am having trouble attaching a db to ms sql express 2005.

I have the db in a folder deep down in my drive, within an asp.net website project. For some wierd reason i can't drill down more than two levels to get to the DB

Anyone have any ideas why?

Ilan

Hey,

Yes, there are security problems for the account that can access it (the SQL Server internal account). I've experienced this as well, and it is weird, but if you copy the DB to c:\program files\microsoft SQL Server\90\, and then into one of the data folders, it can access it there (probably can access from that root folder too).

|||

So you recommend that i copy the DB in the microsft SQL folder and once i have linked to it in express move it to the data folder for my project?

|||

Hey,

It's a minor annoyance, but it's not too bad. Yes, I have found no other thing to do in that situation... The alternatives are to attach the database to the database server, or use straight-up T-SQL to do everything you want. You can use T-SQL to manage your database, as you can use T-SQL for everything you do in the editor.