authentication problem with SQL 2000

Hello guys... I have been struggling for quite some time trying to have the authentication page on my website work :-( and I'm now desperate for some help.

I have used VWD to create a website and I have a login.aspx page that authentiocate users based on their credential... all of it created with the web admin tool.
Now guess what! I have an instance of SQL 2005 running on my PC and it all works smoothly (users are screened and redirected to pages according to their status) but when I'm uploading the website to the company's server which is only authorized to run on SQL 200 I get the following error message:

Server Error in '/newmedical/webtools' Application.

---

Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

Description:An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details:System.Data.SqlClient.SqlException: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

Source Error:

An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.

Stack Trace:

[SqlException (0x80131904): Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.] System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +684883 System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +207 System.Data.SqlClient.TdsParser.Connect(Boolean& useFailoverPartner, Boolean& failoverDemandDone, String host, String failoverPartner, String protocol, SqlInternalConnectionTds connHandler, Int64 timerExpire, Boolean encrypt, Boolean integratedSecurity, SqlConnection owningObject, Boolean aliasLookup) +890 System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance) +601 System.Data.SqlClient.SqlInternalConnectionTds..ctor(SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance) +159 System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection) +108 System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnection owningConnection, DbConnectionPool pool, DbConnectionOptions options) +28 System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject) +445 System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject) +66 System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject) +304 System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection) +85 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) +126 System.Web.DataAccess.SqlConnectionHelper.GetConnection(String connectionString, Boolean revertImpersonation) +239 System.Web.Security.SqlMembershipProvider.GetPasswordWithFormat(String username, Int32& status, String& password, Int32& passwordFormat, String& passwordSalt, Int32& failedPasswordAttemptCount, Int32& failedPasswordAnswerAttemptCount, Boolean& isApproved) +815 System.Web.Security.SqlMembershipProvider.CheckPassword(String username, String password, Boolean updateLastLoginActivityDate, Boolean failIfNotApproved, String& salt, Int32& passwordFormat) +80 System.Web.Security.SqlMembershipProvider.CheckPassword(String username, String password, Boolean updateLastLoginActivityDate, Boolean failIfNotApproved) +42 System.Web.Security.SqlMembershipProvider.ValidateUser(String username, String password) +78 System.Web.UI.WebControls.Login.OnAuthenticate(AuthenticateEventArgs e) +161 System.Web.UI.WebControls.Login.AttemptLogin() +94 System.Web.UI.WebControls.Login.OnBubbleEvent(Object source, EventArgs e) +101 System.Web.UI.Control.RaiseBubbleEvent(Object source, EventArgs args) +35 System.Web.UI.WebControls.Button.OnCommand(CommandEventArgs e) +115 System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) +134 System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +7 System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +11 System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +33 System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +5670

Does ASP.net website management (for roles and membership) create a db: ASPNETDB.mdf ( for user credentials) only accessible by SQL server 2005? If that's the case then no wonder the connection is timing out since the company's server runs on Sql 2000 Entreprise. I can't install SQL server 2005 on the server. Any idea to correct that problem?

Thanks in advance for your tips and advices
JY

Jeewai:

Does ASP.net website management (for roles and membership) create a db: ASPNETDB.mdf ( for user credentials) only accessible by SQL server 2005? If that's the case then no wonder the connection is timing out since the company's server runs on Sql 2000 Entreprise. I can't install SQL server 2005 on the server. Any idea to correct that problem?

No, you can use aspnet_regsql.exe (should be in C:\WINDOWS\Microsoft.NET\Framework\v2.0.xxxx\) to created the database objects required by the membership in any SQL database. Certaintly you can use SQL2000, however you have to do some configuration. Please take a look at this article:

http://weblogs.asp.net/scottgu/archive/2005/08/25/423703.aspx

|||

Thanks so muchIori_Jay for your link to Scott's weblog. I really appreciate the help.
OK, I have run the aspnet_regsql to change my Application Services to use Sql Server 2000 (not Express) on the company's webserver and modified the web.config file to point the machine config file look up the SQL 2000 db for user credentials. I run VWD locally and the application runs fine and is able to connect to the remote company's server to authenticate users.(the newly created tables...aspnet_member and so on... in the webserver db are getting the credentials I've set up using the asp.net web admin tool)

Now after ftping the app to the company's webserver (running SQl 2000) and trying to login on the browser this time I get :

Server Error in '/newmedical' Application.

---

Procedure 'aspnet_Membership_GetPasswordWithFormat' expects parameter '@.UpdateLastLoginActivityDate', which was not supplied.

Description:An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details:System.Data.SqlClient.SqlException: Procedure 'aspnet_Membership_GetPasswordWithFormat' expects parameter '@.UpdateLastLoginActivityDate', which was not supplied.

Source Error:

An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.

Stack Trace:

[SqlException (0x80131904): Procedure 'aspnet_Membership_GetPasswordWithFormat' expects parameter '@.UpdateLastLoginActivityDate', which was not supplied.] System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) +786258 System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +684870 System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +207 System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +1751 System.Data.SqlClient.SqlDataReader.ConsumeMetaData() +37 System.Data.SqlClient.SqlDataReader.get_MetaData() +58 System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) +213 System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) +570 System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) +134 System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method) +32 System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method) +122 System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior) +62 System.Web.Security.SqlMembershipProvider.GetPasswordWithFormat(String username, Int32& status, String& password, Int32& passwordFormat, String& passwordSalt, Int32& failedPasswordAttemptCount, Int32& failedPasswordAnswerAttemptCount, Boolean& isApproved) +815 System.Web.Security.SqlMembershipProvider.CheckPassword(String username, String password, Boolean updateLastLoginActivityDate, Boolean failIfNotApproved, String& salt, Int32& passwordFormat) +80 System.Web.Security.SqlMembershipProvider.CheckPassword(String username, String password, Boolean updateLastLoginActivityDate, Boolean failIfNotApproved) +42 System.Web.Security.SqlMembershipProvider.ValidateUser(String username, String password) +78 System.Web.UI.WebControls.Login.OnAuthenticate(AuthenticateEventArgs e) +161 System.Web.UI.WebControls.Login.AttemptLogin() +94 System.Web.UI.WebControls.Login.OnBubbleEvent(Object source, EventArgs e) +101 System.Web.UI.Control.RaiseBubbleEvent(Object source, EventArgs args) +35 System.Web.UI.WebControls.Button.OnCommand(CommandEventArgs e) +115 System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) +134 System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +7 System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +11 System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +33 System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +5670

Do I have to runaspnet_regsql.exe on the web server or check the .NET framework version which my may not be the same as the one in my development environment? I'm at a loss and any tips would be greatly welcome.

Thanks again in advance for your help. I do believe that others may have encountered the same error and then any help would benefit us all.

Jeewai

|||The new error is a .NET error, not related to the database schema. It indicates to excute the 'aspnet_Membership_GetPasswordWithFormat' stored procedure, you need to give values to the parameters. Can you find where you call the 'aspnet_Membership_GetPasswordWithFormat' procedure? Or it is implictly called by some control?|||

Success ! I found out the technical glitch that was preventing the authentication on the company's webserver. Indeed I updated the webserver's framework to version :v 2.0.50727 which was the one used in my development environment. Then I ran the aspnet_reg utility on the webserver to update the tables and voila...the faulty aspnet_Membership_GetPassword... was fixed and now the application is running wonderfully.

So I hope this short explanation will help a lot of struggling programmers regarding the aforementioned issue. Thanks again to Iori_Jay.

JY

Authentication issue

Hello

I'm using RS 2000 and although Windows Authentication has been selected on the website and the "Reports" virtual directory, when I browse to localhost/reports I get the report manager with only Home on it and a very limited menu bar whixh exists of home, my subcriptions and help. I can't see any folders.

What am I missing?

Thanks!

What rights does your account have in the Report Catalog. What roles does it have assigned to?|||

Thanks for answering Teo

Well, it's hard to tell as I reinstalled everything but things got even worse now.
When I browse to http://localhost/reports, I get:

Access is denied.
Description: An error occurred while accessing the resources required to serve this request. You might not have permission to view the requested resources.

Error message 401.3: You do not have permission to view this directory or page using the credentials you supplied (access denied due to ACLs). Ask the Web server's administrator to give you access to 'C:\Program Files\Microsoft SQL Server\MSSQL\Reporting Services\ReportManager'.

The authentication part is ok because when I deactivate windows authentication, it's just the login box that dissapears but the error above remains so I guess the problem is not authentication related but permission related.

On the virtual directory "Reports" which is 'C:\Program Files\Microsoft SQL Server\MSSQL\Reporting Services\ReportManager', I have the following permissions set up:

Administrator Full rights
Administrators Full rights
Network Service Full rights
ASP.NET Full rights
Everyone Full rights
System Full rights

what more can you do?

|||Looks like you don't have ACL rights to the files in \Program Files\Microsoft SQL Server\MSSQL\Reporting Services\ReportManager. Use the Reporting Services Configuration Tool to apply the default settings to the Report Manager Virtual Directory. This should give the necessary rights to the RS accounts.|||I'm afraid I will have to look for it myself because I'm using RS 2000. The configuration tool does not exist in 2000.|||

Are you connected to some corporate network or it is stand alone PC?

Are you in the Admin group on that machne? if not add yourself to the Admintrators group it should solve the problem.

|||I appologize I missed the RS 2000 part. Grant ASPNET (or Everyone) read access to the Report Manager folder.|||

Well, I reinstalled RS like 7 times and it's working now. Thank you all for the help!

Regards
Worf

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 failed error

Receiving authentication failed error on a SQL driven website.
Basic directory structure

admin
|
|
|--> inc
|
|
|-->setdefaults.inc

inc
|
|
|-->setdefaults.inc and database.mdb

The setdefaults.inc in the admin/inc folder is pointing to the database.mdb in the main inc folder. When I try to access the admin side of the website, I'm getting an authentication failed error. The public side of the website functions properly (displays all files added to the database before the error starting occuring and the search function works properly).

Our ISP migrated their servers about a month ago. Have been unable to confirm with them if the IP or path to the SQL database changed. I don't think it has because the main site still functions properly and it's the same database.

Does anyone have any suggestions on how I can troubleshoot this problem further?

Thanks for your help!

DeborahAre you using the same user accounts to log onto the database for both the admin and customer parts of the site?|||Yes - exactly the same info. The ISP finally got back to me (two months later) and gave me a host name, IP address, database name, UID and PW. The original coding done by my web designer has the fields Server Name, database name, UID and PW.

Do I just replace the Server name with the IP address and do I need to put the host name anywhere. If so, where?

Deborah|||You should be able to replace the Server with the IP address without any problem. Do you know what type of access your account has for the database? The user account may be limited to read only access (or somthing similar) and as a result will not allow the more advanced sections of the admin (update, insert etc).

Originally posted by vascorp
Yes - exactly the same info. The ISP finally got back to me (two months later) and gave me a host name, IP address, database name, UID and PW. The original coding done by my web designer has the fields Server Name, database name, UID and PW.

Do I just replace the Server name with the IP address and do I need to put the host name anywhere. If so, where?

Deborah|||I went line by line through my developers code, it looks like the authentication info on the public side is REMmed out so that solves the mystery as to why it appeared to be authenticating properly on one side and not on the other.

I tried replacing the server name with the IP address. It doesn't work. I just found out that the ISP provider now uses MySQL. Is that entirely different from MS SQL Server?

Thanks

Deborah|||It is a different, and as a result I imagine you will need a different connection string.

You will need somthing like

oConn.Open "Driver={mySQL};" & _
"Server=ipAddress;" & _
"Port=3306;" & _
"Option=131072;" & _
"Stmt=;" & _
"Database=mydb;" & _
"Uid=myUsername;" & _
"Pwd=myPassword"

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.

attempt to execute SLQ not working

I am hosting my website with a web hosting company on the web. My web application reads
data from a SQL server 2005 database. So far I have been able to establish a connection
to the database, but when I attempt to query data from a table, I a get an error message.
I can't figure out what I am doing wrong here.basically I just want to read data from a
column in the database and to store it into a multi-line textbox control. I am using some
inline sql for the call. Can someone help me out here?

public partial class _Default : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e) { }
protected void Button1_Click(object sender, EventArgs e)
{
mySqlConnection = new SqlConnection(ConnectionStringConst);
mySqlConnection.Open();
try
{
// Database is named "Torsion", Table I am querying is named "Content"
SqlCommand cmd = new SqlCommand("SELECT * FROM [Torsion].[dbo].[Content] ORDER BY [Section] DESC", mySqlConnection);
rdr = cmd.ExecuteReader();
ListBox1.DataSource = rdr[0];
ListBox1.DataTextField = "HLContent"; // HLContent is the first column in my database
ListBox1.DataBind(); // I want to store HLContent into ListBox1 control
}
catch { throw;}
finally
{ mySqlConnection.Close();
mySqlConnection.Dispose();
}
}
}
-------------ERROR_MESSAGE_BELOW---------

Server Error in '/' Application.

---

Invalid attempt to read when no data is present.

Description:An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details:System.InvalidOperationException: Invalid attempt to read when no data is present.

Source Error:

An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.

Stack Trace:

[InvalidOperationException: Invalid attempt to read when no data is present.] System.Data.SqlClient.SqlDataReader.GetValue(Int32 i) +137 System.Data.SqlClient.SqlDataReader.get_Item(Int32 i) +7 _Default.Button1_Click(Object sender, EventArgs e) +167 System.Web.UI.WebControls.Button.OnClick(EventArgs e) +105 System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) +107 System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +7 System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +11 System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +33 System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +5102

---

Version Information: Microsoft .NET Framework Version:2.0.50727.832; ASP.NET Version:2.0.50727.832

Can you please check whether data is available in the table.

In the code can please check for the null before assingning to the list box. It is as follows:

rdr = cmd.ExecuteReader();

if(rdr != null)
{

ListBox1.DataSource = rdr[0];
ListBox1.DataTextField = "HLContent"; // HLContent is the first column in my database
ListBox1.DataBind(); // I want to store HLContent into ListBox1 control

}

And also please make sure that the DataReader object is also closed, which is nothing to do with the error but it is recommended for the DataReader.

|||The table does have one record in it. The exception is thrown when

cmd.ExecuteReader();

is executed.

|||

u need to specify whether the commandtype is text or stored procedure

Attaching DB's to Sql Server Management (User Instantances) Error

I have been working on a website in Visual Studio 2005 Pro using the MS SQL Server that comes with VS. I have tried Attaching those MDF files in Visual Studio to the SQL Server Express Management but it wont let me, get an error::

Error Number: 5133
Severity: 16
State: 1
Line Number: 1

TITLE: Microsoft SQL Server Management Studio Express

Attach database failed for Server 'WYATT-PC\SQLEXPRESS'. (Microsoft.SqlServer.Express.Smo)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.3042.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Attach+database+Server&LinkId=20476

ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.Express.ConnectionInfo)

Directory lookup for the file "C:\Users\Wyatt\Documents\Visual Studio 2005\WebSites\AdultUnderWorld\App_Data\aspnetdb.mdf" failed with the operating system error 5(Access is denied.). (Microsoft SQL Server, Error: 5133)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.3042&EvtSrc=MSSQLServer&EvtID=5133&LinkId=20476

BUTTONS:

OK

Here is the info for the Web.config Connection Strings::

<connectionStrings>

<add name="Personal" connectionString="Data Source=.\SQLExpress;Integrated Security=True;User Instance=True;AttachDBFilename=|DataDirectory|Personal.mdf" providerName="System.Data.SqlClient" />

<remove name="LocalSqlServer"/>

<add name="LocalSqlServer" connectionString="Data Source=.\SQLExpress;Integrated Security=True;User Instance=True;AttachDBFilename=|DataDirectory|aspnetdb.mdf" />

</connectionStrings>

I want to attach the above databases into the SQL Server Management Studio,so how can i go about doing this?

You don't attach this database using Management Studio, it is being used by a User Instance and automatically attaches at runtime of the application. By default, Management Studio works against the parent instance of SQL Express, not your User Instance, check out the blog post that documents how to connect to your User Instance with Management Studio. (FYI - This is listed in the FAQ at the top of this forum, always a good place to check every once in a while to see if your question is answered there.)

Mike

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.