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 ticket not received by LogonUser

Hello I am getting an error "Authorization ticket not received by
LogonUser" when trying to render an inline pdf report. It only happens
in VB.Net though.
I think that my authcookie is not being set for some reason.
Some info
I have created this app from scratch(modeled after adventureworks
sample in VB) and modified the adventureworks sample(c#)
the c# sample works but the vb.net does not.
I have set both my webCookie.Domain and netcookie.domain to
".domain.com" <- my domain
The sql server, IIS server and the webreports application are on the
same machine.
If I use the reportViewer control (vs 2003) it works fine and the when
I ask for Request.Cookies.AllKeys
I get
ASP.NET_SessionId
.ASPXAUTH
sqlAuthCookie
as the cookies listed.
This s a good sign but when I call the render function rs.render(all my
parameters) I get the error in vb.net only. Could it be a bad
translation on my part?
THis function may have been translated wrong.
Protected Overloads Overrides Function GetWebResponse(ByVal request As
WebRequest) As WebResponse
Dim response As WebResponse = MyBase.GetWebResponse(request)
Dim cookieName As String = response.Headers("RSAuthenticationHeader")
' If the response contains an auth header, store the cookie
If Not (cookieName Is Nothing) Then
Utilities.CustomAuthCookieName = cookieName
Dim webResponse As HttpWebResponse = CType(response,
HttpWebResponse)
Dim authCookie As Cookie = webResponse.Cookies(cookieName)
' If the auth cookie is null, throw an exception
If authCookie Is Nothing Then
Throw New Exception("Authorization ticket not
received by LogonUser")
End If
' otherwise save it for this request
authCookie = authCookie
' and send it to the client
Utilities.RelayCookieToClient(authCookie)
End If
Return response
End Function 'GetWebResponse
Any sugestions would be much appreciated.
Thanks
MoOne thing I forgot to add is I am using forms authentication and it
seems to be working fine up to the rendering point.|||Are you using forms authentication in the september 2005 CTP by chance?
"M0" wrote:
> One thing I forgot to add is I am using forms authentication and it
> seems to be working fine up to the rendering point.
>

Authentication Problem x64

Win 2003 Server x64, .Net 2.0 x64 Reporting Services 2005. RS Database is a remote SQL 2005 on the same Domain in the same room. I can only get the report manager to authenticate local users; it will not authenticate Domain users.If I set up a local user and add a New Role Assignment all works OK.I have no problem adding a Domain user to a New Role Assignment, it allows this, but it will not authenticate the user.Adding the Domain user to the Windows Administrators group also has no effect.

Am I missing something simple?

I doubt this has anything to do with the x64-ness of your HW.

When you say "it will not authenticate the user", what exactly do you mean?

|||

If I try to log on to http://mymachine/reports with a local user it will let me on.If I try to log on as a Domain user, it keeps bringing up the logon screen. (Windows Authentication)Both users are set up in a role in RS.

|||It sounds to me like IIS is not recognizing the user. Does that user have permissions on the machine aside from being in the RS role?|||If you are asking about the Domain users, NO. I am expecting it to work like Share Point, where you add the Domain user in the site and give it permissions on the site. Am I assuming wrong?|||

Can you check the IIS logs on the machine for when the domain user tries to connect?

SRS doesn't grant the user any permissions on the box, so if the user did not have a valid account on the machine before, he still won't be able to have access to SRS because IIS will fail the request before it even gets to SRS.

|||

John,

I see the Get entry in the IIS log, no other entries. I see a success audit entry in the Security Log for that user.

If I log on with the the local user, I see the POST entries etc in the IIS log.

Do I need to set up the domain user to have some local rights?

|||I finally wipped the machine clean and started again. Now RS will authenticate a domain user fine.

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 issue with report called from VB.Net application

Hi,
I've written an application in VB.Net 2005. One of its requirements is to
display a report which is hosted on our report server, and to let people
subscribe to this report (to get a copy once a week by email, for example).
To do this I have created a windows form containing a web browser control,
and set the URL of that control to the URL of the report, which happens to
be:
http://10.9.200.18/Reports/Pages/Report.aspx?ItemPath=%2fBunker+Management+Reports%2fActive+Bunker+Schemes+Due+To+Expire
Whenever I open that form I get prompted for my windows credentials. Also,
if I go straight to that URL in a web browser, I also get prompted for my
credentials. However, if I start from
http://10.9.200.18/Reports I can navigate to the same report without being
prompted for my credentials.
What can I do to be able to jump straight to the report without being
prompted for credentials? I switched on the option to enable anonymous
access for the web site itself (this is an internal web server), which did
the trick, but then I couldn't access the report server via either
http://10.9.200.18/Reports - took me to the report server home page, but
didn't list any contents
or
http://10.9.200.18/ReportServer - told me that the anonymous user account
didn't have permission to access the resource.
Enabling anonymous access wasn't exactly ideal, anyway. For one thing it
applied to the whole site, not just the report, and for another the user are
all going to be authenticated users anyway, so...
Kev"Kevin O'Donovan" <kev1609@.community.nospam> wrote in message
news:uScqcSAsGHA.4208@.TK2MSFTNGP05.phx.gbl...
> Hi,
> I've written an application in VB.Net 2005. One of its requirements is to
> display a report which is hosted on our report server, and to let people
> subscribe to this report (to get a copy once a week by email, for
> example). To do this I have created a windows form containing a web
> browser control, and set the URL of that control to the URL of the report,
> which happens to be:
> http://10.9.200.18/Reports/Pages/Report.aspx?ItemPath=%2fBunker+Management+Reports%2fActive+Bunker+Schemes+Due+To+Expire
> Whenever I open that form I get prompted for my windows credentials. Also,
> if I go straight to that URL in a web browser, I also get prompted for my
> credentials. However, if I start from
> http://10.9.200.18/Reports I can navigate to the same report without being
> prompted for my credentials.
> What can I do to be able to jump straight to the report without being
> prompted for credentials? I switched on the option to enable anonymous
> access for the web site itself (this is an internal web server), which did
> the trick, but then I couldn't access the report server via either
> http://10.9.200.18/Reports - took me to the report server home page, but
> didn't list any contents
> or
> http://10.9.200.18/ReportServer - told me that the anonymous user
> account didn't have permission to access the resource.
> Enabling anonymous access wasn't exactly ideal, anyway. For one thing it
> applied to the whole site, not just the report, and for another the user
> are all going to be authenticated users anyway, so...
> Kev
Okay, solved this ourselves, though if anyone can explain why, I'd be
grateful. When I refer to the web server via its IP address I get prompted
for credentials. When I refer to it by its DNS name I don't get prompted. I
can resolve my immediate problem by using the name in the URL, and adding
that name to the remote user's site's DNS, but I'd like to understand why
the two URLs are treated differently?
Kevin

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

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 trail - Sql Server 2000

Hi,
Greetings to all.
We are building up a new application using SQL Server 2000 & .NET.
Need to build basic “Audit trail” process, in which need to store {User_
Id/
DateTime/ Status/Process Name}.
What’s the best strategy for implementing “Audit trail” in SQL Server
2000
Applications?
Thanks for your time.I'm assuming that "Status" and "Process Name" are not columns in tables, but
rather logical names in your application. The simplest way is to use
something like .NET Enterprise Library or even build your own simple logic
in your application to keep trace of this.
Typically, in the database, if you want to track changes made to your
tables, you use triggers, but in your case, I think you want to track what
"functions" a user is using?
--
HTH,
SriSamp
Email: srisamp@.gmail.com
Blog: http://blogs.sqlxml.org/srinivassampath
URL: http://www32.brinkster.com/srisamp
"SSUK" <SSUK@.newsgroups.nospam> wrote in message
news:7C422ADD-588A-4B34-BD0B-727C6F625BF9@.microsoft.com...
> Hi,
> Greetings to all.
> We are building up a new application using SQL Server 2000 & .NET.
> Need to build basic "Audit trail" process, in which need to store
> {User_Id/
> DateTime/ Status/Process Name}.
> What's the best strategy for implementing "Audit trail" in SQL Server 2000
> Applications?
> Thanks for your time.
>|||Thanks for your response. Let me explain my situation once again:-
e.g. I have a screen & different status of order on that screen i.e.
Created/Billed/Processed/Manufactured/Shipped.
What I want to do is, I want to record, which user had changed above order
status at what time?
Which means for each record, I want to build an Audit Trail?
I am aware that ,I can create my own Audit table & can create DB Triggers
for recording details ,or stored Proc can do same for me…
I am looking for what’s the best strategy to build an Audit trail.
Thank you very much for your precious time & Valuable inputs.
Regards.
"SriSamp" wrote:

> I'm assuming that "Status" and "Process Name" are not columns in tables, b
ut
> rather logical names in your application. The simplest way is to use
> something like .NET Enterprise Library or even build your own simple logic
> in your application to keep trace of this.
> Typically, in the database, if you want to track changes made to your
> tables, you use triggers, but in your case, I think you want to track what
> "functions" a user is using?
> --
> HTH,
> SriSamp
> Email: srisamp@.gmail.com
> Blog: http://blogs.sqlxml.org/srinivassampath
> URL: http://www32.brinkster.com/srisamp
> "SSUK" <SSUK@.newsgroups.nospam> wrote in message
> news:7C422ADD-588A-4B34-BD0B-727C6F625BF9@.microsoft.com...
>
>|||I would suggest triggers and audit tables. Try to keep the trigger as light
as possible. What kind of load (# of users, volume of data, etc.) does your
system have?
Alain Quesnel
alainsansspam@.logiquel.com
www.logiquel.com
"SSUK" <SSUK@.newsgroups.nospam> wrote in message
news:ADC87BEF-D824-49DD-B840-304CD99C332D@.microsoft.com...
> Thanks for your response. Let me explain my situation once again:-
> e.g. I have a screen & different status of order on that screen i.e.
> Created/Billed/Processed/Manufactured/Shipped.
> What I want to do is, I want to record, which user had changed above order
> status at what time?
> Which means for each record, I want to build an Audit Trail?
> I am aware that ,I can create my own Audit table & can create DB Triggers
> for recording details ,or stored Proc can do same for me.
> I am looking for what's the best strategy to build an Audit trail.
> Thank you very much for your precious time & Valuable inputs.
> Regards.
>
> "SriSamp" wrote:
>|||Hi,
Apart from Trigger & Audit table ,does sql server comes with default
keywords like AUdit ? Or any other Standard/better approach to hadle it ?
Oracle allows us to audit data using the ‘AUDIT’ command. For example,
‘AUDIT DELETE ON my_table;’
Regards
"Alain Quesnel" wrote:

> I would suggest triggers and audit tables. Try to keep the trigger as ligh
t
> as possible. What kind of load (# of users, volume of data, etc.) does you
r
> system have?
> --
> Alain Quesnel
> alainsansspam@.logiquel.com
> www.logiquel.com
>
> "SSUK" <SSUK@.newsgroups.nospam> wrote in message
> news:ADC87BEF-D824-49DD-B840-304CD99C332D@.microsoft.com...
>
>|||Hi,
In terms of Users/Load ,it's 50 -75 users using it at one point.
"Alain Quesnel" wrote:

> I would suggest triggers and audit tables. Try to keep the trigger as ligh
t
> as possible. What kind of load (# of users, volume of data, etc.) does you
r
> system have?
> --
> Alain Quesnel
> alainsansspam@.logiquel.com
> www.logiquel.com
>
> "SSUK" <SSUK@.newsgroups.nospam> wrote in message
> news:ADC87BEF-D824-49DD-B840-304CD99C332D@.microsoft.com...
>
>|||I don't think there are any default tools, but I'd personally steer
clear of triggers. While they're great at doing what they do, and lend
themselves naturally to an audit trail, I've found the main drawback of
them is that you can't see them. When someone else comes to maintain
your application, they would have to know that the triggers are there.
I know that with good documentation and skilled staff this would appear
simple, but you can't generally count on either.
My prefered method (given this drawback of triggers) is to write a
sufficiently generic auditing stored procedure, then within your insert
/ update / delete stored procs call this method. This has the advantage
that your auditing behaviour is centralised, and therefore if you need
to change it you won't need to iterate through the triggers, but also
that the operation is visible to anyone else who needs to pick up your
app.
I suspect this is a sufficiently controversial suggestion to get
utterly thrashed on here now, so... off you go guys, above the belt
with the punches please.|||Thanks for your reply.I agree to your suggestion to quite some extent.But i
am wondering , do we have an better way / inbuilt way of handling this ? Or
any more standard way (Best practise) of handling this ?
"Will" wrote:

> I don't think there are any default tools, but I'd personally steer
> clear of triggers. While they're great at doing what they do, and lend
> themselves naturally to an audit trail, I've found the main drawback of
> them is that you can't see them. When someone else comes to maintain
> your application, they would have to know that the triggers are there.
> I know that with good documentation and skilled staff this would appear
> simple, but you can't generally count on either.
> My prefered method (given this drawback of triggers) is to write a
> sufficiently generic auditing stored procedure, then within your insert
> / update / delete stored procs call this method. This has the advantage
> that your auditing behaviour is centralised, and therefore if you need
> to change it you won't need to iterate through the triggers, but also
> that the operation is visible to anyone else who needs to pick up your
> app.
> I suspect this is a sufficiently controversial suggestion to get
> utterly thrashed on here now, so... off you go guys, above the belt
> with the punches please.
>|||I don't know of any in-built methods, I'm pretty sure there aren't any.
However the information you'll be recording is usually fairly specific
to your app. My advice would be either to maintain auditing in the
stored procs, or, depending on the structure of your web app you could
enhance your data access layer to add a custom audit module that
controls all the auditing.
Personally I prefer keeping out of SQL server based auditing for
several reasons:
1) you can more easily re-use your auditing code
2) you can more easily audit to a centralised database for several apps
3) by auditing to a separate database you are not having to back up
tracing data along with your actual app data (though you do have to
consider re-synchronisation when restoring)
4) you are able to capture web app info more easily (e.g. if you're
using forms based authentication you can capture usernames, or you can
capture the page that the update originated from).
5) the auditing is then done for your application, rather than on your
db. This has the advantage that if someone else makes a custom update
to your database, you can't accuse the application of it. On the other
hand, you don't have the traceability of what was done (but personally
I prefer the idea that your app only audits things that it does).
6) the auditing is more visible and more maintainable
I could probably come up with more reasons, but this should indicate my
preference.
Perhaps someone else could recommend some off the shelf plug-ins for
this?
Will|||Just to give you a couple of more options to evaluate:
Have a server-side profiler trace going in which you capture the relevant ev
ents.
Use some of the 3:rd party (transaction) log reader tools, where some has ex
plicit audit
capabilities. The transaction log contains information about all modificatio
ns and some of these
tools can also log SELECT using a profiler trace in conjunction with the tra
nsaction log. I've
listed some of these log reader tools on my links page:
http://www.karaszi.com/SQLServer/links.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"SSUK" <SSUK@.newsgroups.nospam> wrote in message
news:7C422ADD-588A-4B34-BD0B-727C6F625BF9@.microsoft.com...
> Hi,
> Greetings to all.
> We are building up a new application using SQL Server 2000 & .NET.
> Need to build basic "Audit trail" process, in which need to store {User_Id
/
> DateTime/ Status/Process Name}.
> What's the best strategy for implementing "Audit trail" in SQL Server 2000
> Applications?
> Thanks for your time.
>

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.

Attaching a database

We have an install created with Wise that installs the .net framework and
MSDE 2000 silently. Everything seems to work fine on the install of these
two packages.
After we install MSDE and we make sure the service has started, we need to
attach a database. We do that by executing a batch file that looks like
this:
osql -E -S(local)\<myinstance> -Q "sp_attach_db @.dbname = N'X2', @.filename1
= N'C:\Program Files\<appname>\x2.mdf', @.filename2 = N'C:\Program
Files\<appname>\x2.ldf'"
After all of this executes and I try to run my application it says that the
login fails because the sa password is incorrect. We are executing the MSDE
install setup via command line like:
SETUP INSTANCENAME="<myinstance>" SECURITYMODE=sql SAPWD="password" /qn
In order to get the app to run, we have to go doubleclick on the batch file
(shown above).
Any ideas why the execution of this batch file might not be working the
first time?
Thanks.
STom
hi Stom,
"STom" <stombiztalker@.hotmail.com> ha scritto nel messaggio
news:ejRS$aM9EHA.1300@.TK2MSFTNGP14.phx.gbl
> We have an install created with Wise that installs the .net framework
> and MSDE 2000 silently. Everything seems to work fine on the install
> of these two packages.
> After we install MSDE and we make sure the service has started, we
> need to attach a database. We do that by executing a batch file that
> looks like this:
> osql -E -S(local)\<myinstance> -Q "sp_attach_db @.dbname = N'X2',
> @.filename1 = N'C:\Program Files\<appname>\x2.mdf', @.filename2 =
> N'C:\Program Files\<appname>\x2.ldf'"
> After all of this executes and I try to run my application it says
> that the login fails because the sa password is incorrect. We are
> executing the MSDE install setup via command line like:
> SETUP INSTANCENAME="<myinstance>" SECURITYMODE=sql SAPWD="password"
> /qn
> In order to get the app to run, we have to go doubleclick on the
> batch file (shown above).
> Any ideas why the execution of this batch file might not be working
> the first time?
> Thanks.
> STom
I do no clearly understand your question... I prepared a .Bat file as
following,
osql -E -S(local) -Q"EXEC sp_attach_db @.dbname = N'a', @.filename1 =
N'C:\Programmi\Microsoft SQL Server\MSSQL\Data\a.mdf', @.filename2 =
N'C:\Programmi\Microsoft SQL Server\MSSQL\Data\a_log.LDF'" >c:\err.txt
and it works as expected...
you say
> After all of this executes and I try to run my application it says
> that the login fails because the sa password is incorrect.
and this seems to me not related with the execution of sp_attach_db, but
perhaps my english is to poor to understand your meaning...
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.9.1 - DbaMgr ver 0.55.1
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||Sorry for the confusion.
What I mean is that when that batch file is initially executed, by the Wise
utility, it does not appear to work, because when I run the app, it says if
fails for login 'sa'.
If I then go double-click on this batch file, and run the app, the app runs
fine.
Notice that the batch file does not set any passwords. It just attaches the
database, so I don't think the problem is with the login, its with the
attaching.
The question is, why would it not work if the Wise utility executes it?
STom
"Andrea Montanari" <andrea.sqlDMO@.virgilio.it> wrote in message
news:347n53F431apgU1@.individual.net...
> hi Stom,
> "STom" <stombiztalker@.hotmail.com> ha scritto nel messaggio
> news:ejRS$aM9EHA.1300@.TK2MSFTNGP14.phx.gbl
> I do no clearly understand your question... I prepared a .Bat file as
> following,
> osql -E -S(local) -Q"EXEC sp_attach_db @.dbname = N'a', @.filename1 =
> N'C:\Programmi\Microsoft SQL Server\MSSQL\Data\a.mdf', @.filename2 =
> N'C:\Programmi\Microsoft SQL Server\MSSQL\Data\a_log.LDF'" >c:\err.txt
> and it works as expected...
> you say
> and this seems to me not related with the execution of sp_attach_db, but
> perhaps my english is to poor to understand your meaning...
> --
> Andrea Montanari (Microsoft MVP - SQL Server)
> http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
> DbaMgr2k ver 0.9.1 - DbaMgr ver 0.55.1
> (my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
> interface)
> -- remove DMO to reply
>
|||hi,
"STom" <stombiztalker@.hotmail.com> ha scritto nel messaggio
news:%23KK1qaN9EHA.3616@.TK2MSFTNGP11.phx.gbl
> Sorry for the confusion.
> What I mean is that when that batch file is initially executed, by
> the Wise utility, it does not appear to work, because when I run the
> app, it says if fails for login 'sa'.
thi is another problem, I think...
osql -E indicates to log in MSDE using osql with a trusted WinNT
connections... and this is a thing..

> If I then go double-click on this batch file, and run the app, the
> app runs fine.
> Notice that the batch file does not set any passwords. It just
> attaches the database, so I don't think the problem is with the
> login, its with the attaching.
> The question is, why would it not work if the Wise utility executes
> it?
I actually do not know how Wise installs MSDE... perhaps the MSDE
installation is still pending and not finished... you've probably better
check this with Wise people...
sorry
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.9.1 - DbaMgr ver 0.55.1
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||STom wrote:
> What I mean is that when that batch file is initially executed, by the Wise
> utility, it does not appear to work, because when I run the app, it says if
> fails for login 'sa'.
>
Hi STom,
try to capture the output which is produced by the batch file
to see. Maybe osql is not yet in the path or something similar.
HTH,
Gerald

attached in vb8 vs vb6 incompatability

I have two programs which access sql server 2005. One was written in vb6 the other in vb8 (aka vb.net 2005).

vb6 works well with this connection string:

Provider=SQLNCLI;Integrated Security=SSPI;Persist Security Info=False;Database=MyDbName;Data Source=.\MyServerInstanceName

vb8 (in vs2005) generated the following connection string which works well in the vb8 application:

Data Source=.\MyServerInstanceName;AttachDbFilename='C:\MyDbPath\MyDbName.mdf';Integrated Security=True

Within their own programs each connection string works well. However, if I use one program and then the other (e.g., vb8 program then vb6), I get an error message:

Unable to open the physical file "C:\MyDbPath\MyDbName.mdf". Operating system error 32: "32(The process cannot acces the file because it is being used by another process.)".

If I detach the database using SSMS, I can then access it with the other program. Obviously, this is not an ideal situation and not something I would want the end user to have to do.

Also, when using the vb6 connection string, SSMS lists the database in Object Explorer as: MyDbName. When using the vb8, the database is listed as C:\MyDbPath\MyDbName.mdf. BTW, the database is set for multi-user.

How can I get the two programs to place nice together?

YOu can either use the database file with one process or attach the database to a SQL Server (Express) instance to make it accessible for more than one application.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

When you use the attach command in the connection string the application that is running the command has exclusive access to the files, If you want the database to be used by multiple applications you will need to first make sure that the database will allow remote connections then attach the database to the database engine using the management tools or the command line tools. Once this is done you would then just connect using the following string

"Data Source=Aron1;Initial Catalog=pubs;Integrated Security=SSPI;"

The above connection string was taken from www.connectionstrings.com

|||

The database absolutely must be used by more than one program. SQL Server Express is not an option because of its 4gb limit. Perhaps I'm not understanding something here. It sounds as tho you are saying an SQL Server database cannot be used by more than one program at the same time unless the database is so small that it is unusable for all except the simplest of applications.

|||

Again, perhaps I am missing something. Are you saying SQL Server 2005 cannot be used by multiple users unless they are all using the same program, or they have enough database management experience to use tools such as SSMS, or even more shocking still: they have to use command line tools? This is starting sound like Linux and MySQL.

It is shocking to imagine that everyday users would have to have such extensive skills just to use database enabled Windows XP? applications.

|||Yes, you are right, you got me wrong :-)

OK, a database file which is not attached can be attached as a user instance with a SQL Server Express instance. As a user instance, only the application which opened the connection to the user instance is able to access the database. If you attach the database file to a server instance, you can access the data through the SQL Server Express Service as you already know it from the common SQL Server database access.

Hope that clarifies things.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||Sorry, but no. I don't understand why you are talking about SQL Server Express. I'm not using the Express version; it is totally unusable for the project I'm working on.|||I was talking about Express because you are using the user instance feature.|||

Where am I using the user instance feature?

|||Data Source=.\MyServerInstanceName;AttachDbFilename='C:\MyDbPath\MyDbName.mdf';Integrated Security=True|||

Now, I am confused ! The .\MyServerInstanceName refers to an instance of SQL Server 2005 and not .\sqlexpress . Also, in VS2005, if I try to change the connection string to User Instance = True (it is currently set to False), I get the following error message:

The user instance login flag is not supported on this version of SQL Server. The connection will be closed.

|||I resolved the problem by starting both vb6 & vb8 programs with a check for whether or not the database is attached. If it is not, I use sp_attach_single_file_db to attach it. Then I changed the part of the vb8 connection AttachDbFilename= to Database=.

AttachDbName Relative Directory

I have host my ASP.NET application on gate.com, and there I have access to a SQL Server 2005 instance, and I am trying to make relative paths in my web.config work, (|Data Directory|), but I don't know how to change the default value of |Data Directory|, and it isn't the root of my application, I'm pretty sure. My setup is like so, and the file was created using SQL express 2005, I am not sure if that is my problem, because gate uses the full version.

/<root>/App_Data/Leads.mdf

here is the stack trace:

[SqlException (0x80131904): An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)]
System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +735091
System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +188
System.Data.SqlClient.TdsParser.Connect(Boolean& useFailoverPartner, Boolean& failoverDemandDone, String host, String failoverPartner, String protocol, SqlInternalConnectionTds connHandler, Int64 timerExpire, Boolean encrypt, Boolean trustServerCert, Boolean integratedSecurity, SqlConnection owningObject, Boolean aliasLookup) +820
System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance) +628
System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance) +170
System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection) +130
System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnection owningConnection, DbConnectionPool pool, DbConnectionOptions options) +28
System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject) +424
System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject) +66
System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject) +496
System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection) +82
System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory) +105
System.Data.SqlClient.SqlConnection.Open() +111
System.Web.DataAccess.SqlConnectionHolder.Open(HttpContext context, Boolean revertImpersonate) +84
System.Web.DataAccess.SqlConnectionHelper.GetConnection(String connectionString, Boolean revertImpersonation) +197
System.Web.Profile.SqlProfileProvider.GetPropertyValuesFromDatabase(String userName, SettingsPropertyValueCollection svc) +766
System.Web.Profile.SqlProfileProvider.GetPropertyValues(SettingsContext sc, SettingsPropertyCollection properties) +428
System.Configuration.SettingsBase.GetPropertiesFromProvider(SettingsProvider provider) +410
System.Configuration.SettingsBase.GetPropertyValueByName(String propertyName) +117
System.Configuration.SettingsBase.get_Item(String propertyName) +89
System.Web.Profile.ProfileBase.GetInternal(String propertyName) +36
System.Web.Profile.ProfileBase.get_Item(String propertyName) +68
System.Web.Profile.ProfileBase.GetPropertyValue(String propertyName) +4
ProfileCommon.get_Cart() in c:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\Temporary ASP.NET Files\root\27a74ff4\376615bd\App_Code.ceobor1b.4.cs:102
Default2.UpdateTotal() in \\shared.hosting.local\nfs\cust\9\80\45\754089\web\Order.aspx.cs:45
Default2.Page_Load(Object sender, EventArgs e) in \\shared.hosting.local\nfs\cust\9\80\45\754089\web\Order.aspx.cs:37
System.Web.Util.CalliHelper.EventArgFunctionCaller(IntPtr fp, Object o, Object t, EventArgs e) +15
System.Web.Util.CalliEventHandlerDelegateProxy.Callback(Object sender, EventArgs e) +34
System.Web.UI.Control.OnLoad(EventArgs e) +99
System.Web.UI.Control.LoadRecursive() +47
System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +1061

Hi LiquidTobi,

Could you show me your connection string for the connection?

|||Actually, I figured out a workaround, I used the aspnet_regsql tool to register the database that is hosted with gate, and I transferred my tables and views to it as well, so I no longer need to attach the mdf file.

AttachDBFileName option in Connection String

I used the following connection string when working with a SQL server DB locally in an ASP.NET code-behind module:
dim source as string="data source=.\SQLEXPRESS;Integrated Security=SSPI;AttachDBFilename=|DataDirectory|Registration.mdf;User Instance=true"

However, when I uploaded the application to a web server where my site is hosted, I spent considerable time debugging the "Cannnot establish connection" exception, I was getting.

After adding "Database=Registration.mdf;server=<server N/W address>" and removing
"AttachDBFilename=|DataDirectory|Registration.mdf;" from the connection string, the connection was finally established.

Now, I have two questions: What's the difference between database and attachDBFileName options (And when should I use which)?

Secondly, why I had to add the server option, when it worked fine locally, and the application & db are located on the same web server?

You might look following link. you need to provide the database name along with the DB filename

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

|||Are database name & DB Filename not the same? Do you mean complete DB Path when you say DB FileName?
Moreover, what is an attachable Database? The primary file of an attachable database is the default primary data file or it can be any primary data file?

Also the ODBC driver Connection String mentions following:
"The database must be an existing database unless AttachDBFileName is also specified. If AttachDBFileName is also specified, the primary file it points to is attached and given the database name specified by Database"

Does that mean that if Database attribute contains a non-existent DB name, but AttachDBFileName contains a valid primary filename, then a database is created by that name?
|||

Here.. what u need to understand is ... u r using the unique Embedded Database feature of SQL Server Express. U r dynamically attaching the database.

Refer this link it will help u

Connecting to SQL Express User Instances in Management Studio
http://blogs.msdn.com/sqlexpress/archive/2006/11/22/connecting-to-sql-express-user-instances-in-management-studio.aspx

Madhu|||

AttachDBFilename is unique to SQL Express, it spins up a user instance of SQL Express attached to a specific DB Filename for single user mode. Database is simply the name of the database to use, it has no additional connotation. For any production server, you would most likely not be using AttachDBFilename. It is strictly useful for development and experimentation in single-user mode.

You had to change data source=.\SQLEXPRESS on the production server because it did not have a named instance of SQL Express running on it. The syntax of a server name is <Server>\<instance>. Note that a blank <instance> is equated to the default instance. In your case, the web server is running a default instance of SQL Server. The option to install a default instance is available in SQLEXPRESS as well, though you have to select it explicitly or else it installs as a named instance with the name SQLEXPRESS. You could have used (local) or localhost or . or <COMPUTERNAME> instead of the IP on the server, so long as you don't specify an absent instance name.

Hope that cleared things up for you,

John

AttachDBFileName option in Connection String

I used the following connection string when working with a SQL server DB locally in an ASP.NET code-behind module:
dim source as string="data source=.\SQLEXPRESS;Integrated Security=SSPI;AttachDBFilename=|DataDirectory|Registration.mdf;User Instance=true"

However, when I uploaded the application to a web server where my site is hosted, I spent considerable time debugging the "Cannnot establish connection" exception, I was getting.

After adding "Database=Registration.mdf;server=<server N/W address>" and removing
"AttachDBFilename=|DataDirectory|Registration.mdf;" from the connection string, the connection was finally established.

Now, I have two questions: What's the difference between database and attachDBFileName options (And when should I use which)?

Secondly, why I had to add the server option, when it worked fine locally, and the application & db are located on the same web server?

You might look following link. you need to provide the database name along with the DB filename

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

|||Are database name & DB Filename not the same? Do you mean complete DB Path when you say DB FileName?
Moreover, what is an attachable Database? The primary file of an attachable database is the default primary data file or it can be any primary data file?

Also the ODBC driver Connection String mentions following:
"The database must be an existing database unless AttachDBFileName is also specified. If AttachDBFileName is also specified, the primary file it points to is attached and given the database name specified by Database"

Does that mean that if Database attribute contains a non-existent DB name, but AttachDBFileName contains a valid primary filename, then a database is created by that name?
|||

Here.. what u need to understand is ... u r using the unique Embedded Database feature of SQL Server Express. U r dynamically attaching the database.

Refer this link it will help u

Connecting to SQL Express User Instances in Management Studio
http://blogs.msdn.com/sqlexpress/archive/2006/11/22/connecting-to-sql-express-user-instances-in-management-studio.aspx

Madhu|||

AttachDBFilename is unique to SQL Express, it spins up a user instance of SQL Express attached to a specific DB Filename for single user mode. Database is simply the name of the database to use, it has no additional connotation. For any production server, you would most likely not be using AttachDBFilename. It is strictly useful for development and experimentation in single-user mode.

You had to change data source=.\SQLEXPRESS on the production server because it did not have a named instance of SQL Express running on it. The syntax of a server name is <Server>\<instance>. Note that a blank <instance> is equated to the default instance. In your case, the web server is running a default instance of SQL Server. The option to install a default instance is available in SQLEXPRESS as well, though you have to select it explicitly or else it installs as a named instance with the name SQLEXPRESS. You could have used (local) or localhost or . or <COMPUTERNAME> instead of the IP on the server, so long as you don't specify an absent instance name.

Hope that cleared things up for you,

John