Long wait times for simple SQL on 2005

I have an existing application deployed on many SQL 2000 servers running without issue. We installed SQL 2005 (8 way DualCore) and the most simple trivial SQL statements are taking an extreme amount of time (e.g. an identical query in 2000 took 10 ms is taking a couple of minutes in 2005). The data in the tables being queried are very small (low row count < 400; and low row size < 1k).

When profiling 2005 we see the connection going into a status of 'suspended' with a wait type of 'ASYNC_NETWORK_IO'. There just simply is not enough data being retrieved for a lag to occur returning data to the client. Has anyone seen anything like this? Any thoughts?

I haven't seen anything relating to NETWORK_IO, one gotcha is not doing index rebuilds. http://sqlblogcasts.com/blogs/simons/archive/2006/10/20/SQL-2005-UpgradeRebuild-your-indexes-.aspx

Can you include more details on the queries.

If you run the queries in SSMS do they still take a long time?

How many users are using the server? Is there server doing anything else?

|||

This is a new client of ours (installing our web app, which, has been in production for a few years and we have no issues in house on our 2005 or 2000 SQL boxes) so there are no current active users (save the one that I was using to test with); so the current activity on the server is virtually nill. This is one of our default databases (again, virtually no data except for so default info in the one table I am querying) - all indexes are up to date.

The table has simple varchar columns and two text columns (the data in the columns vary from 100 bytes to 10k [the high end]. Here is teh defintion of the table:

Table [REPORT_DEF]
[REPORT_GU] [uniqueidentifier] NOT NULL,
[NAME] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[NAMESPACE] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[NUMBER] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[PAGE_ORIENTATION] [varchar](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[OWNER_TYPE] [varchar](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ORGANIZATION_GU] [uniqueidentifier] NULL,
[XML_DEF] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[CLASS_NAME] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ASSEMBLY] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[COMPANY_GU] [uniqueidentifier] NULL,
[LOCATION] [nvarchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ICON] [nvarchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[TITLE] [nvarchar](60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[SHORT_TITLE] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[LOCALE] [nvarchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[REPORT_VIEW_TYPE] [nvarchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[MODULE] [nvarchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[DEPLOY_VERSION] [numeric](7, 0) NOT NULL DEFAULT ((0)),
[MAIN_REPORT_RPX] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ASSOCIATED_REPORT_GU] [uniqueidentifier] NULL,
CONSTRAINT PRIMARY KEY CLUSTERED - Column: REPORT_GU
CONSTRAINT UNIQUE NONCLUSTERED - columns: NAMESPACE and NAME

The query that is being run is as follows:

SELECT R0.[COMPANY_GU],R0.[NUMBER],R0.[NAME],R0.[ASSOCIATED_REPORT_GU],R0.[MAIN_REPORT_RPX],
R0.[REPORT_GU],R0.[PAGE_ORIENTATION],R0.[SHORT_TITLE],R0.[ASSEMBLY],R0.[MODULE],R0.[TITLE],
R0.[DEPLOY_VERSION],R0.[NAMESPACE],R0.[LOCATION],R0.[ORGANIZATION_GU],R0.[ICON],R0.[OWNER_TYPE],
R0.[XML_DEF],R0.[LOCALE],R0.[REPORT_VIEW_TYPE],R0.[CLASS_NAME]
FROM [REPORT_DEF] R0

ORDER BY R0.[NAMESPACE] ASC,R0.[NAME] ASC,R0.[REPORT_GU] ASC

Again, when I execute this query on the DB server (via Mgmt Studio) it executes in about 80 ms (give or take a couple ms). When run on a machine on the same network (gigabit backbone, the only two machines on this network) the execution time jumps to 75 seconds (give or take 5 seconds).

Thoughts?

|||

Can you run profiler and capture the XML plans, and then compare them, to check they are getting the same plan.

Also be aware that by default SSMS uses the in memory data provider which will give performance improvements compared with running on the client.

How much data is being returned?

Are you using SSMS on the client as well?

|||

Simon,

Thanks for your thoughts on this one. I broke down and called MS and after about 6 hours we found that there was a network issue. Both SQL and the web server machines have gigabit NICs but the switch that the client had the two machines plugged into is a 10/100 switch...with the NICs set to autosensing basic network traffic was extremely delayed (tested via explorer copying a 70MB file to the SQL server occured in 8 seconds, from the SQL server to the web sever was taking about 35 minutes). Anyway, chalk this one up to experience and something to look for next time.

Thanks for your help!

Long time SSIS package loading

I load SSIS package using following code:

Application app = new Application();
Package pac = app.LoadFromSqlServer(packageName, serverName, null, null, null);

For simple package containing 2 tasks this code executes about 20 seconds.
If I load old version (SQL2K) package from SQL2000 then it takes 5 seconds.

Is any way to increase loading speed for SSIS packages?

Do you have SSIS service running? The service caches SSIS component information; in the absense of the service it may take long time (although it is usually 2-3 seconds on reasonable hardware) to enumerate them when loading the package.|||

Thank you Michael.
I carry out additional experiments with realworld package (contains 11 tasks) at another computer.
When I start this experiment I have to wait 1.5 minutes to load package every time. Then I examine if SSIS running. I discover SSIS don't running after SP1 was applied (widely known SP1 bug). I installed post SP1 cumulative hotfixes and continue investigation. Now package loading first time during 30 seconds. When I start it second time it takes about 2 seconds. This is good result. But is there ability to increase loading speed at first time? Will be loading perfomance improved if such delay caused internal implementation of SSIS?

|||Much of the time on the initial load of the package is due to validation of the metadata of each of your tasks. In order to speed of the initial load, you could change the DelayValidation property of your package and all tasks to true. However, that's not really best practices.|||

Thank you Martin
This helps.

BTW, BOL says:
"Validating the package before it runs is a way of finding some errors before execution starts. However, it processes the package to find errors, and if no errors are found, the package runs. Because this goes through the package two times, validating a package increases the amount of processing for the package, so should be used only when necessary."

|||

Alexey Rokhin wrote:

... I discover SSIS don't running after SP1 was applied (widely known SP1 bug). I installed post SP1 cumulative hotfixes and continue investigation. Now package loading first time during 30 seconds...

The bug you quote is described in http://support.microsoft.com/kb/918644. While the hotfix fixes the service startup failure, it can't fix the cause of the problem - long time to start the service due to network configuration that timeouts requests to CRL. So the service still takes 30+ seconds to start (which caused to fail before the hotfix, this later part is now fixed).

To avoid this 30+ delay you should either change the SSIS service to autostart (thus moving the delay to the computer boot time), or better fix the network configuration as described in the KB (although it might be very specific to your environment) to either allow access to CRL, or to quickly fail and return failure status to an application accessing CRL.

|||

Thank you, Michael.
In production environment SSIS service will autostart.

|||

Alexey,

IMHO, a good practice is defining a Events class and so, debugging the code and so you'll find issues faster, i.e with F11...

pkg = app.LoadFromSqlServer(ObjSSIS.sRutaDts & "\" & ObjSSIS.sSSISName, ObjSSIS.sServer, "usrSSIS", "ninot", EventsSSIS)

Public Class EventsSSIS

OnError

OnPreExecute

..

..

sql

Long table locks

Hi
There is an application that runs on sql server.
The application selects/updates some few tables frequently
Once there is even a select on this table .It blocks other users
sometimes for very long.
Is there anything that can be done to reduce this?
The table has 18000 rows and does not seem to have an index
I thought indexing might help but 18000 rows without an index is
no reason for 30 minutes of lock time.
I will appreciate your help as usual
VinceVincento Harris (wumutek@.yahoo.com) writes:
> There is an application that runs on sql server.
> The application selects/updates some few tables frequently
> Once there is even a select on this table .It blocks other users
> sometimes for very long.
> Is there anything that can be done to reduce this?
> The table has 18000 rows and does not seem to have an index
> I thought indexing might help but 18000 rows without an index is
> no reason for 30 minutes of lock time.

Yes, something can probably be done, but the information you've provided
is not sufficient to say what should be done.

The key to nail down performance problems is information. Exactly
what is taking long time? And are there any transactions that are
open for all this time? You might get some useful information by
tracing this process with the Profiler.

Is this ia an in-house app, or a something you've bought from a vendor?

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

long running transaction blocking queue

Hello,

I seem to be misunderstanding the way transactions work with service broker queues. We have developed and deployed a service broker application that 5 queues and a windows service for each queue on multiple servers (3 currently). Due to a last minute issue, we had to not use transactions when the services executed a recieve and I am not updating the code base to use transactions and am running into blocking issues. One of the services runs for 90 seconds (spooling to the printer) and all of the servers block on the receive operation for this queue. I thought that if I was receving messages from a single conversation, other receives against this queue would not block.

Thanks,

Jim Stallings

RECEIVE indeed should now block each other. One RECEIVE should either return messages for an unlocked conversation or return an empty resultset. Can you share how you do the RECEIVEs? E.g, are you using WAITFOR, are you using a WHERE clause, are you using TOP etc. Also, how many conversations and how many messages per conversation typically are in the queue?

Can you look what is the lock contention on? sys.processes will show what is the type and name of the resource a thread is blocked on.

Thanks,
~ Remus

|||Hello Remus,
Thanks for your response. I took a long weekend so I didn't see your response Friday. At the moment I'm just doing a receive top (1) (count passed in) as this is a very simple implementation where each message has it's own queue and I receive one per message per access until an end conversation is encountered. I (and others) were under the impression from how SSB is presented, that long running transacations could be left open on one conversation without blocking others but your answere indicates that is not the case? My problem is that I need to have a transaction around the processing of the message so that I don't have to build recovery logic if the windows service fails to complete.

From the PDC demo I saw, they showed that you could be processing from one conversation group and other processes could still access another conversation group. That led me to the conclusion that locking is by conversation group. Is that not true? Am I supposed to receive by conversation group? Is receiving by conversation handle supposed to prevent blocking of subsequent receives?

I worked with our DBA to track down the blocking and it is occurring in my stored procedure that does the receive. The sequence is that windows service A (server A) does a receive in a transaction. Windows service B (server B, etc) does another receive and blocks on a select by conversation handle. This select by the way is a holdover from the original coding I converted from the PDC code and is not used anywhere. We went from code complete to production so fast I didn't have time to go back and trim this kind of unused feature from the conversion. I can easily modify the receieve to select by conversation group if this would help with the blocking but since when we did the analysis we saw that we were blocking on the clustered index of the internal table, I had doubts if this would make a difference so I decided to post and ask. My receive proc is below and in the case that I'm asking about, I'm passing a count of 1, no timeout so no waitfor is generated, and no conversation handle.

Thanks,
Jim

ALTER PROCEDURE [DDPT].[ssb_Receive]
/*
****************************************************************************************************************
* File Name : DDPT.ssb_Receive.sql
* Description: Execute RECEIVE against the specified queue. Wait time of 0 will return immediately
* if no messages in queue, non-zero wait time will perform waitfor of specified wait time.
* Receive will be for any conversation unless converstation specified. Message count can
* be specified to limit the number of messages received.
*
* Author : Jim Stallings
*
* Example : Exec DDPT.ssb_Receive.sql
@.queueName = 'LabelRequestQueue'
, @.serverName = 'WSHB8257'
*
* History : Date Initials Description
* - -- --
* 03/13/2006 JAS Initial Development
*
****************************************************************************************************************
*/
@.queueName NVARCHAR(255)
, @.conversationId UNIQUEIDENTIFIER = NULL
, @.waitTime INT = NULL
, @.messageCount INT = 0
, @.serverName NVARCHAR(128)= NULL
AS
BEGIN

SET NOCOUNT ON;

DECLARE @.strSQL NVARCHAR(2000), @.errNo INT
DECLARE @.conversation_handle uniqueidentifier
, @.message_type_name nvarchar(128)
, @.message xml
, @.service_name nvarchar(128)
, @.taskid bigint

SELECT @.strSQL = 'SELECT top 1 @.conversation_handle = conversation_handle,
@.service_name = service_name, @.message_type_name = message_type_name, @.message = message_body
FROM ' + CAST(@.queueName AS VARCHAR(255))

SELECT @.strSQL = @.strSQL + ' WITH (NOLOCK) WHERE conversation_handle = ' + CAST(@.conversationId AS VARCHAR)
WHERE @.conversationId IS NOT NULL

EXEC sp_executesql @.strSQL
, N'@.conversation_handle UNIQUEIDENTIFIER OUTPUT, @.service_name nvarchar(128) output, @.message_type_name NVARCHAR(128) OUTPUT, @.message xml OUTPUT'
, @.conversation_handle OUTPUT, @.service_name OUTPUT, @.message_type_name OUTPUT, @.message OUTPUT

SET @.strSQL = ''
IF @.waitTime IS NOT NULL
SET @.strSQL = 'WAITFOR('

SELECT @.strSQL = @.strSQL + 'RECEIVE '
IF @.messageCount != 0
SELECT @.strSQL = @.strSQL + 'TOP(' + CAST(@.messageCount as varchar) + ')'

SELECT @.strSQL = @.strSQL
+ 'conversation_group_id, conversation_handle
, message_sequence_number, service_name
, service_contract_name, message_type_name
, validation, message_body
FROM ' + CAST(@.queueName AS VARCHAR(255))

SELECT @.strSQL = @.strSQL + 'WHERE conversation_handle = ' + CAST(@.conversationId AS VARCHAR)
WHERE @.conversationId IS NOT NULL

IF @.waitTime IS NOT NULL
SELECT @.strSQL = @.strSQL + '), TIMEOUT ' + CAST(@.waitTime AS VARCHAR)

EXEC(@.strSQL)
SELECT @.errNo = @.@.Error

RETURN @.errNo

END|||

Hi Remus,

I wanted to correct my last post. When I said the demo showed that you could receive from one group while other processes could receive from another, I mispoke. I meant that one of the PDC presentations I listened to gave me the impression that this was capability provided by conversation group locking in that once you started to receive against a conversation group, another process receiving against the same queue would return messages from the next group. I'm assuming that for this to function, a transaction must be kept open against the group until all messages are processed.

Also, I forgot to add that when we looked at the blocking when doing the receive, we determined that we were blocking on the clustered index on the internal table.

Jim

|||

I'm not sure I understand the logic of this procedure. But one thing I can tell is that looking up a conversation by means of SELECT and building a RECEIVE WHERE based on the SELECT return is not going to work. Concurrent procedure calls will return the same conversation and your RECEIVEs will block each other, because they'll try to lock the same conversation (as you already found out).

Also, mixing RECEIVE with and without a WHERE clause on the same queue is not going to work. The RECEIVEs without a WHERE clause will 'steal' messages you expect to be received by the RECEIVEs with a WHERE clause. The typical pattern to use is to use WAITFOR(RECEIVE ...) without a WHERE clause and then process the messages. This pattern allows the RECEIVE to get the next available conversation.

My recommandation is to remove the optional @.conversationId and simplify the procedure to use a simple RECEIVE without a WHERE clause. This way you can benefit from the conversation group locking and the built-in capability of RECEIVE to find the next available conversation group.

BTW you should really consider whether using dynamic T-SQL in a stored procedure is justified in your case. Doing so negates much of the performance benefits of stored procs.

HTH,
~ Remus

|||

Hi Remus,

First, many DBA's have been pounding into me that there are no performance benefits to stored procs since they are compiled on the fly now. Is this not true for SQL 2005?

The logic behind proc was to provide a general purpose receive stored proc that could be used by multiple services with and without timeouts and/or conversation handles. While this could result in the mixed access you mentioned, the respective services (and therefore queues) would consistently either use or not use converstation handles (and timeouts with waitfor). I don't know how else to do a receive proc that works for multiple queues and I don't want to build a stored proc for every queue.

The convoluted logic you mention is logic I said in the post was initially coded, not used and should have been (and is going to be) tossed. In any case, it was just to do a lookup in the queue , and if the conversation handle was not found, the result of executing the select would have nulled the conversation id parameter so that it would not be built into the subsequent receive command. BTW, I thought I read a post from someone in the service broker group that optimizer hints work against the internal tables for service broker and if that is incorrect that would be nice to know. Doing the receive as you suggest is not an issue, that is what I had decided to do anyway. All messages are independent and followed by an end conversation message.

Anyway, down to the real (and I apologize for my poor description of the problem) issue, it is that the windows service that executes for 90 seconds processing a specific message. I have read and heard that service broker could manage a conversation that takes seconds, minutes or even weeks to complete. If a queue is locked for the duration of a transaction, how would this be implemented without single threading on that queue? That is exactly what we are looking for but locking the queue for the 90 seconds duration means that I can't have another server start working on another message. Taking the processing out of the transaction means that I have to do additional coding to track failures and resend messages.

Jim

|||

Jim,

Lets do a simple experiment using the Management Studio.

1. First, create two queues and two services in your database of choice:

[code]

create queue [initiator];

create queue [target];

create service [from] on queue [initiator];

create service [to] on queue [target] ([DEFAULT]);

go

[/code]

2. Now lets send two messages, on two distinct dialogs, by running the following snippet twice:

[code]

declare @.dh uniqueidentifier;

begin dialog conversation @.dh

from service [from]

to service N'to', N'current database'

with encryption = off;

send on conversation @.dh;

go

[/code]

3. On a new Query, issue a RECEIVE and don't commit (to simulate an arbitrarily long transaction). This will grab the first enqueued message:

[code]

begin transaction;

receive * from [target];

-- commit;

[/code]

4. On a new Query, issue a second RECEIVE and don't commit (to simulate an arbitrarily long transaction). This will grab the second enqueued message:

[code]

begin transaction;

receive * from [target];

-- commit;

[/code]

5. On a new Query, issue a third RECEIVE, with a WAITFOR and don't commit. This will block waiting for a message:

[code]

begin transaction;

waitfor (receive * from [target])

-- commit;

[/code]

6. Run again the snippet from step 2 to send a new message.

7. Switch back to the query window opened at step 6. The WAITFOR has now completed, returning the newly enqueued message.

8. Look at the transaction locks will show how each RECEIVE has concurently locked diferent conversation groups:

[code]

select * from sys.dm_tran_locks

where resource_subtype='CONVERSATION_GROUP'

[/code]

This example shows that one can issue concurent RECEIVEs and they will no block each other. If you are seeing in your case RECEIVEs being blocked by each other, that would normally indicate that those RECEIVEs have a WHERE clause and are competing for the same conversation.

HTH,
~ Remus

|||

Hi Remus,

This is what I was asking.

Thanks,

Jim

|||

Jim Stallings wrote:

First, many DBA's have been pounding into me that there are no performance benefits to stored procs since they are compiled on the fly now. Is this not true for SQL 2005?

A stored proc that contains a statement (e.g. a RECEIVE statement) will be compiled once, cached and then at execution time the said statement execution will be invoked. If instead, at the same position, the proc contains an EXEC @.sqlBatch, or an sp_executesql @.sqlBatch, then at execution time the T-SQL batch that is contained in @.sqlBatch has to be parsed, compiled and executed. Parsing and compilation are steps that take significant time, comparable with execution time.

If the @.sqlBatch is identical each time (character by charatcter), then the system might already have a cached ad-hoc compiled plan for it from last time it was executed. But if the batch is but a single character different, it would have to be parsed and compiled from scratch each time. If the @.sqlBatch is constructed with an embeded GUID value, then you're guaranteed to have a different @.sqlBatch each time, causing a parsing and compilation from scratch. If dynamic SQL is a must, a good practice is to construct the @.sqlBatch with and embeded parameter and use sp_executesql instead of EXEC (since sp_executesql can pass in parameters).

A good read on the subject is: http://www.microsoft.com/technet/prodtechnol/sql/2005/recomp.mspx. Have those DBAs read it too ;-)

HTH,
~ Remus

long running queries quit - audit logout

I have a web based application that launches sql queries using ADO from
ASP/VBScript. Typically 2 - 10 queries are executed in a loop, and the
result sets are sent off to Crystal Reports at the end of the run with the
report being displayed in a browser window. In general this all works well
is has been very reliable.
Lately I've been having trouble with some of these queries on databases that
have gotten quite large (25 - 40G) but only a couple of the tables have
rowcounts in the tens of millions, most are relatively small. The behavior
is that the first query runs, sometimes for up to 90 minutes, seems to
complete, and then the pid is gone. Looking at the profile trace, I can see
that the query executed nearly 13 M reads with a duration of over 90
minutes. The line following the SQL:BatchCompleted event is Audit Logout,
and thats the last trace of the process. Under "normal" operation, the next
query would start and they would all run until the loop is complete. I've
seen this now with 2 completely different sets of queries, on 2 different
dbs, yet the symptoms are the same. It completes a very simple query that
returns a single datetime value. Then the first "real" query runs, anywhere
from 60 - 90 minutes or more, and then the process goes away.
It seems as if I've reached some threshold or internal timeout. I have the
connection time and query timeouts set to very large values (1000 minutes),
so it's not that.The client process is of course IIS. This is running SQL
Server 2000 SP3, NT 4.0, IIS 5.5.
I have had sets of queries that have taken as long as 3 hours to complete,
but each individual query probably never exceeded 45 minutes in duration.
Can anyone think of what is causing this to happen -- why does the pid go
away after the first query completes? Is there some other internal timeout
somewhere that I'm running into?
Any advice is appreciated,
-GaryYou might be experiencing lock_timeout. See if it helps:
http://msdn.microsoft.com/library/en-us/tsqlref/ts_set-set_1yr8.asp
"Gary" <gld@.hotmail.com> wrote in message
news:bisSc.243431$IQ4.52808@.attbi_s02...
> I have a web based application that launches sql queries using ADO from
> ASP/VBScript. Typically 2 - 10 queries are executed in a loop, and the
> result sets are sent off to Crystal Reports at the end of the run with the
> report being displayed in a browser window. In general this all works well
> is has been very reliable.
> Lately I've been having trouble with some of these queries on databases
that
> have gotten quite large (25 - 40G) but only a couple of the tables have
> rowcounts in the tens of millions, most are relatively small. The behavior
> is that the first query runs, sometimes for up to 90 minutes, seems to
> complete, and then the pid is gone. Looking at the profile trace, I can
see
> that the query executed nearly 13 M reads with a duration of over 90
> minutes. The line following the SQL:BatchCompleted event is Audit Logout,
> and thats the last trace of the process. Under "normal" operation, the
next
> query would start and they would all run until the loop is complete. I've
> seen this now with 2 completely different sets of queries, on 2 different
> dbs, yet the symptoms are the same. It completes a very simple query that
> returns a single datetime value. Then the first "real" query runs,
anywhere
> from 60 - 90 minutes or more, and then the process goes away.
> It seems as if I've reached some threshold or internal timeout. I have the
> connection time and query timeouts set to very large values (1000
minutes),
> so it's not that.The client process is of course IIS. This is running SQL
> Server 2000 SP3, NT 4.0, IIS 5.5.
> I have had sets of queries that have taken as long as 3 hours to complete,
> but each individual query probably never exceeded 45 minutes in duration.
> Can anyone think of what is causing this to happen -- why does the pid go
> away after the first query completes? Is there some other internal timeout
> somewhere that I'm running into?
> Any advice is appreciated,
> -Gary
>
>|||Thanks for the tip. I checked a server and it's returning -1 (infinite) for
this. I'm thinking that the problem is the client connection to the database
dropping sometime during the processing of a query, and when the statement
complete event occurs, SQL detects that the connection is gone and therefore
the audit logoff event.
"oj" <nospam_ojngo@.home.com> wrote in message
news:O7lkZQ$fEHA.3632@.TK2MSFTNGP09.phx.gbl...
> You might be experiencing lock_timeout. See if it helps:
> http://msdn.microsoft.com/library/en-us/tsqlref/ts_set-set_1yr8.asp
>

Long Character Strings

Greetings,

I have a problem that I'm not sure how to handle. In the application I am working on I will be importing fixed length strings from a CD into the database. Each specific character in the string represents some value.

I can't decide if I should just create a single field of 895 characters or create 895 single character fields. When I need data from the database I won't need all 895 characters of information, I'll only need one or a small subset of values.

Does it really matter which approach I take?

Thanks in advance for any advice/tips you can provide.

ZackIt depends on what you plan to do with the data. If you are doing a bit of manipulation of each field for each record, at random intervals, and you want to be able to quickly look up (visually) an indexed value by using enterprise manager or a spreadsheet tool.. you're probably better off making 895 fields (you might want to find a script to do this.. that would take forever in design view).

If you are doing lots of data manipulation in chunks, then just make the single field and buffer it while you adjust the values, then update. In that situation you're gonna have better performance. You will not be able to easily look up an indexed value visually by doing it this way, so you might have to make a special tool if this is important too.

Like I say, it all depends on how you plan to use the data.

Brentsql

Logon failure executing reports in SRS2005

Environment: Application server with WS2003, IIS 6.0 and Reporting Services
2005; Database server with WS2003 and SQL Server 2005.
Each time I want to execure a report I get the following error message:
Logon failed. -->
Logon failure: the user has not been granted the requested logon type at
this computer. (Exception from HRESULT: 0x80070569)
Reporting Services Configuration tool does not show any errors.
Web Service and Windows Service identity both use NT AUTHPORITY/NETWORK
SERVICE.
Database Connection uses sa.
It does not make any difference from where I execute the report, application
server, client, Report Manager, Report Builder, always the same login
fauilure.What user is the Application Pool for the report virtual directories running
under?
I did have lots of problems with a RS server where someone had used a domain
user as the identity for the application pool. This domain user did not have
the access rigth to "Log on as a service", which was needed. I changed the
identity to run as network service, and all the errors went away. Might help
for you as well.
Kaisa M. Lindahl Lervik
"ChrisWF" <ChrisWF@.discussions.microsoft.com> wrote in message
news:0E9D8B8F-2A31-4731-B7B0-9F7D6348D3A1@.microsoft.com...
> Environment: Application server with WS2003, IIS 6.0 and Reporting
> Services
> 2005; Database server with WS2003 and SQL Server 2005.
> Each time I want to execure a report I get the following error message:
> Logon failed. -->
> Logon failure: the user has not been granted the requested logon type at
> this computer. (Exception from HRESULT: 0x80070569)
> Reporting Services Configuration tool does not show any errors.
> Web Service and Windows Service identity both use NT AUTHPORITY/NETWORK
> SERVICE.
> Database Connection uses sa.
> It does not make any difference from where I execute the report,
> application
> server, client, Report Manager, Report Builder, always the same login
> fauilure.|||They run under the Network Service account.
But your response triggered me to check once more the execution account (a
domain account) I use to perform unattended operations. When I added this
account to the administrators group it worked. Probably not the right
solution but at least I could move on with the testing.
Thanks,
Chris
"Kaisa M. Lindahl Lervik" wrote:
> What user is the Application Pool for the report virtual directories running
> under?
> I did have lots of problems with a RS server where someone had used a domain
> user as the identity for the application pool. This domain user did not have
> the access rigth to "Log on as a service", which was needed. I changed the
> identity to run as network service, and all the errors went away. Might help
> for you as well.
> Kaisa M. Lindahl Lervik
> "ChrisWF" <ChrisWF@.discussions.microsoft.com> wrote in message
> news:0E9D8B8F-2A31-4731-B7B0-9F7D6348D3A1@.microsoft.com...
> > Environment: Application server with WS2003, IIS 6.0 and Reporting
> > Services
> > 2005; Database server with WS2003 and SQL Server 2005.
> >
> > Each time I want to execure a report I get the following error message:
> >
> > Logon failed. -->
> > Logon failure: the user has not been granted the requested logon type at
> > this computer. (Exception from HRESULT: 0x80070569)
> >
> > Reporting Services Configuration tool does not show any errors.
> > Web Service and Windows Service identity both use NT AUTHPORITY/NETWORK
> > SERVICE.
> > Database Connection uses sa.
> >
> > It does not make any difference from where I execute the report,
> > application
> > server, client, Report Manager, Report Builder, always the same login
> > fauilure.
>
>

Logon failed. (rsLogonFailed)

Hi,
I am writing a web application in c# that is attempting to get the
parameters from a report on a Reporting Services 2005 server.
I am using the following code:
SQLReportViewer.ServerReport.ReportServerCredentials = new
ReportCredentials(UserName, UserPassword, UserDomain);
SQLReportViewer.ServerReport.ReportPath = Rpt.FileName;
SQLReportViewer.ServerReport.ReportServerUrl = new Uri(ServerUrl);
Parameters = SQLReportViewer.ServerReport.GetParameters();
The Getparameters Line causes the following exception:
Microsoft.Reporting.WebForms.ReportServerException was unhandled by user
code
Message="Logon failed. (rsLogonFailed)"
Source="Microsoft.ReportViewer.WebForms"
ErrorCode="rsLogonFailed"
StackTrace:
at Microsoft.Reporting.WebForms.ServerReport.get_Service()
at Microsoft.Reporting.WebForms.ServerReport.GetExecutionInfo()
at Microsoft.Reporting.WebForms.ServerReport.GetParameters()
The UserName, UserPassword and UserDomain variables are all populated
with correct data, as is Rpt.FileName.
The ReportCredentials class is a very simple implementation of the
ICredentials interface - Code shown here:
-->
public class ReportCredentials : IReportServerCredentials
{
protected string _UserName, _Password, _Domain;
public ReportCredentials(string UserName, string Password, string Domain)
{
_UserName = UserName;
_Password = Password;
_Domain = Domain;
}
//*****************************************************************
public bool GetFormsCredentials (out System.Net.Cookie AuthCookie,
out string UserName, out string Password, out string Authority)
{
UserName = _UserName;
Password = _Password;
Authority = _Domain;
AuthCookie = null;
return (true);
}
//*****************************************************************
public System.Security.Principal.WindowsIdentity ImpersonationUser
{
get { return (null); }
}
//*****************************************************************
public ICredentials NetworkCredentials
{
get{ return (new NetworkCredential(_UserName, _Password, _Domain)); }
}
<--
I can use the same credentials to retrieve a list of reports from the
report server (the report I am trying to access is in that list) and to
log on directly by going to the URL of Reporting Services.
This is made more frustrating by the complete lack of detail in the
error message, as it does not say why the logon failed, or give me a
single clue about what is going on.
Has anyone seen this before and can tell me how to fix it, or is there
something stupid in my code?
Thankyou.
PaulTwo points. What role is your user in? It could be that perhaps you need to
be in a different role to get information about the report than you do to
get a list of reports? It seems to me if it worked for a list of reports
then your login must be OK.
Second issue. For something like this I recommend trying to web forum. The
other MVP is there and is familiar with webservices. Also, that is where MS
people hang out.
http://forums.microsoft.com/msdn/showforum.aspx?forumid=82&siteid=1
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Paul Cheetham" <PAC.News@.dsl.pipex.com> wrote in message
news:%231p4s096HHA.5164@.TK2MSFTNGP05.phx.gbl...
> Hi,
> I am writing a web application in c# that is attempting to get the
> parameters from a report on a Reporting Services 2005 server.
> I am using the following code:
> SQLReportViewer.ServerReport.ReportServerCredentials = new
> ReportCredentials(UserName, UserPassword, UserDomain);
> SQLReportViewer.ServerReport.ReportPath = Rpt.FileName;
> SQLReportViewer.ServerReport.ReportServerUrl = new Uri(ServerUrl);
> Parameters = SQLReportViewer.ServerReport.GetParameters();
> The Getparameters Line causes the following exception:
>
> Microsoft.Reporting.WebForms.ReportServerException was unhandled by user
> code
> Message="Logon failed. (rsLogonFailed)"
> Source="Microsoft.ReportViewer.WebForms"
> ErrorCode="rsLogonFailed"
> StackTrace:
> at Microsoft.Reporting.WebForms.ServerReport.get_Service()
> at Microsoft.Reporting.WebForms.ServerReport.GetExecutionInfo()
> at Microsoft.Reporting.WebForms.ServerReport.GetParameters()
>
> The UserName, UserPassword and UserDomain variables are all populated with
> correct data, as is Rpt.FileName.
> The ReportCredentials class is a very simple implementation of the
> ICredentials interface - Code shown here:
>
> -->
> public class ReportCredentials : IReportServerCredentials
> {
> protected string _UserName, _Password, _Domain;
> public ReportCredentials(string UserName, string Password, string Domain)
> {
> _UserName = UserName;
> _Password = Password;
> _Domain = Domain;
> }
> //*****************************************************************
> public bool GetFormsCredentials (out System.Net.Cookie AuthCookie, out
> string UserName, out string Password, out string Authority)
> {
> UserName = _UserName;
> Password = _Password;
> Authority = _Domain;
> AuthCookie = null;
> return (true);
> }
> //*****************************************************************
> public System.Security.Principal.WindowsIdentity ImpersonationUser
> {
> get { return (null); }
> }
> //*****************************************************************
> public ICredentials NetworkCredentials
> {
> get{ return (new NetworkCredential(_UserName, _Password, _Domain)); }
> }
> <--
> I can use the same credentials to retrieve a list of reports from the
> report server (the report I am trying to access is in that list) and to
> log on directly by going to the URL of Reporting Services.
> This is made more frustrating by the complete lack of detail in the error
> message, as it does not say why the logon failed, or give me a single clue
> about what is going on.
> Has anyone seen this before and can tell me how to fix it, or is there
> something stupid in my code?
> Thankyou.
> Paul
>|||OK Thanks, I'll post there.
I'm pretty sure the Role is OK, as I know the same user details are
being used successfully in another application, which I am replacing,
but that is just showing the report and is not trying to get the
Parameter list.
Paul
Bruce L-C [MVP] wrote:
> Two points. What role is your user in? It could be that perhaps you need to
> be in a different role to get information about the report than you do to
> get a list of reports? It seems to me if it worked for a list of reports
> then your login must be OK.
> Second issue. For something like this I recommend trying to web forum. The
> other MVP is there and is familiar with webservices. Also, that is where MS
> people hang out.
> http://forums.microsoft.com/msdn/showforum.aspx?forumid=82&siteid=1
>

Logon failed. (rsLogonFailed)

Hi,

I am writing a web application in c# that is attempting to get the parameters from a report on a Reporting Services 2005 server.

I am using the following code:

SQLReportViewer.ServerReport.ReportServerCredentials = new
ReportCredentials(UserName, UserPassword, UserDomain);
SQLReportViewer.ServerReport.ReportPath = Rpt.FileName;
SQLReportViewer.ServerReport.ReportServerUrl = new Uri(ServerUrl);

Parameters = SQLReportViewer.ServerReport.GetParameters();

The Getparameters Line causes the following exception:

Microsoft.Reporting.WebForms.ReportServerException was unhandled by user code
Message="Logon failed. (rsLogonFailed)"
Source="Microsoft.ReportViewer.WebForms"
ErrorCode="rsLogonFailed"
StackTrace:
at Microsoft.Reporting.WebForms.ServerReport.get_Service()
at Microsoft.Reporting.WebForms.ServerReport.GetExecutionInfo()
at Microsoft.Reporting.WebForms.ServerReport.GetParameters()

The UserName, UserPassword and UserDomain variables are all populated with correct data, as is Rpt.FileName.
The ReportCredentials class is a very simple implementation of the ICredentials interface - Code shown here:

-->

public class ReportCredentials : IReportServerCredentials
{
protected string _UserName, _Password, _Domain;

public ReportCredentials(string UserName, string Password, string Domain)
{
_UserName = UserName;
_Password = Password;
_Domain = Domain;
}

//*****************************************************************

public bool GetFormsCredentials (out System.Net.Cookie AuthCookie, out string UserName, out string Password, out string Authority)
{
UserName = _UserName;
Password = _Password;
Authority = _Domain;
AuthCookie = null;
return (true);
}

//*****************************************************************

public System.Security.Principal.WindowsIdentity ImpersonationUser
{
get { return (null); }
}

//*****************************************************************

public ICredentials NetworkCredentials
{
get{ return (new NetworkCredential(_UserName, _Password, _Domain)); }
}

<--

I can use the same credentials to retrieve a list of reports from the report server (the report I am trying to access is in that list) and to log on directly by going to the URL of Reporting Services.

This is made more frustrating by the complete lack of detail in the error message, as it does not say why the logon failed, or give me a single clue about what is going on.

Has anyone seen this before and can tell me how to fix it, or is there something stupid in my code?

Thankyou.

Paul

Does the same thing happen when you do this:

Code Snippet

CredentialCache cc = new CredentialCache();

cc.Add(new Uri(Settings.Default.ReportServerEndPoint),"Basic", new NetworkCredential("uid", "pwd"));

reportViewer.ServerReport.ReportServerCredentials.NetworkCredentials = cc;

|||Hi,

ReportServerCredentials.NetworkCredentials is read only, and so cannot be assigned to.

Thankyou.

|||

You are right of course. Unline the WinForms version, the ASP.NET version of ReportViewer has NetworkCredentials read-only. Upon a second look, I don't see your code implementing IReportServerCredentials.GetBasicCredentials.

Code Snippet

using System;
using System.Collections.Generic;
using System.Text;
using Microsoft.Reporting.WinForms;
using System.Security.Principal;

public class ReportViewerCredentials : IReportServerCredentials
{

public ReportViewerCredentials()
{
}

public ReportViewerCredentials(string username)
{
this.Username = username;
}

public ReportViewerCredentials(string username, string password)
{
this.Username = username;
this.Password = password;
}

public ReportViewerCredentials(string username, string password, string domain)
{
this.Username = username;
this.Password = password;
this.Domain = domain;
}

public string Username
{
get
{
return this.username;
}
set
{
string username = value;
if (username.Contains("\\"))
{
this.domain = username.Substring(0, username.IndexOf("\\"));
this.username = username.Substring(username.IndexOf("\\") + 1);
}
else
{
this.username = username;
}
}
}
private string username;

public string Password
{
get
{
return this.password;
}
set
{
this.password = value;
}
}
private string password;

public string Domain
{
get
{
return this.domain;
}
set
{
this.domain = value;
}
}
private string domain;

#region IReportServerCredentials Members

public bool GetBasicCredentials(out string basicUser, out string basicPassword, out string basicDomain)
{
basicUser = username;
basicPassword = password;
basicDomain = domain;
return username != null && password != null && domain != null;
}

public bool GetFormsCredentials(out string formsUser, out string formsPassword, out string formsAuthority)
{
formsUser = username;
formsPassword = password;
formsAuthority = domain;
return username != null && password != null && domain != null;

}

public WindowsIdentity ImpersonationUser
{
get
{
return null;
}
}

#endregion
}

|||Hi,

After much messing about and testing, I finally tracked it down to a problem in the GetformsCredentials function in my Credentials class.

By changing it to the following, everything works.

public bool GetFormsCredentials (out System.Net.Cookie AuthCookie, out string UserName, out string Password, out string Authority)
{
UserName = null;
Password = null;
Authority = null;
AuthCookie = null;

return (false);
}

Thanks

Paul

Logon failed. (rsLogonFailed)

Hi,

I am writing a web application in c# that is attempting to get the parameters from a report on a Reporting Services 2005 server.

I am using the following code:

SQLReportViewer.ServerReport.ReportServerCredentials = new
ReportCredentials(UserName, UserPassword, UserDomain);
SQLReportViewer.ServerReport.ReportPath = Rpt.FileName;
SQLReportViewer.ServerReport.ReportServerUrl = new Uri(ServerUrl);

Parameters = SQLReportViewer.ServerReport.GetParameters();

The Getparameters Line causes the following exception:

Microsoft.Reporting.WebForms.ReportServerException was unhandled by user code
Message="Logon failed. (rsLogonFailed)"
Source="Microsoft.ReportViewer.WebForms"
ErrorCode="rsLogonFailed"
StackTrace:
at Microsoft.Reporting.WebForms.ServerReport.get_Service()
at Microsoft.Reporting.WebForms.ServerReport.GetExecutionInfo()
at Microsoft.Reporting.WebForms.ServerReport.GetParameters()

The UserName, UserPassword and UserDomain variables are all populated with correct data, as is Rpt.FileName.
The ReportCredentials class is a very simple implementation of the ICredentials interface - Code shown here:

-->

public class ReportCredentials : IReportServerCredentials
{
protected string _UserName, _Password, _Domain;

public ReportCredentials(string UserName, string Password, string Domain)
{
_UserName = UserName;
_Password = Password;
_Domain = Domain;
}

//*****************************************************************

public bool GetFormsCredentials (out System.Net.Cookie AuthCookie, out string UserName, out string Password, out string Authority)
{
UserName = _UserName;
Password = _Password;
Authority = _Domain;
AuthCookie = null;
return (true);
}

//*****************************************************************

public System.Security.Principal.WindowsIdentity ImpersonationUser
{
get { return (null); }
}

//*****************************************************************

public ICredentials NetworkCredentials
{
get{ return (new NetworkCredential(_UserName, _Password, _Domain)); }
}

<--

I can use the same credentials to retrieve a list of reports from the report server (the report I am trying to access is in that list) and to log on directly by going to the URL of Reporting Services.

This is made more frustrating by the complete lack of detail in the error message, as it does not say why the logon failed, or give me a single clue about what is going on.

Has anyone seen this before and can tell me how to fix it, or is there something stupid in my code?

Thankyou.

Paul

Does the same thing happen when you do this:

Code Snippet

CredentialCache cc = new CredentialCache();

cc.Add(new Uri(Settings.Default.ReportServerEndPoint),"Basic", new NetworkCredential("uid", "pwd"));

reportViewer.ServerReport.ReportServerCredentials.NetworkCredentials = cc;

|||Hi,

ReportServerCredentials.NetworkCredentials is read only, and so cannot be assigned to.

Thankyou.

|||

You are right of course. Unline the WinForms version, the ASP.NET version of ReportViewer has NetworkCredentials read-only. Upon a second look, I don't see your code implementing IReportServerCredentials.GetBasicCredentials.

Code Snippet

using System;
using System.Collections.Generic;
using System.Text;
using Microsoft.Reporting.WinForms;
using System.Security.Principal;

public class ReportViewerCredentials : IReportServerCredentials
{

public ReportViewerCredentials()
{
}

public ReportViewerCredentials(string username)
{
this.Username = username;
}

public ReportViewerCredentials(string username, string password)
{
this.Username = username;
this.Password = password;
}

public ReportViewerCredentials(string username, string password, string domain)
{
this.Username = username;
this.Password = password;
this.Domain = domain;
}

public string Username
{
get
{
return this.username;
}
set
{
string username = value;
if (username.Contains("\\"))
{
this.domain = username.Substring(0, username.IndexOf("\\"));
this.username = username.Substring(username.IndexOf("\\") + 1);
}
else
{
this.username = username;
}
}
}
private string username;

public string Password
{
get
{
return this.password;
}
set
{
this.password = value;
}
}
private string password;

public string Domain
{
get
{
return this.domain;
}
set
{
this.domain = value;
}
}
private string domain;

#region IReportServerCredentials Members

public bool GetBasicCredentials(out string basicUser, out string basicPassword, out string basicDomain)
{
basicUser = username;
basicPassword = password;
basicDomain = domain;
return username != null && password != null && domain != null;
}

public bool GetFormsCredentials(out string formsUser, out string formsPassword, out string formsAuthority)
{
formsUser = username;
formsPassword = password;
formsAuthority = domain;
return username != null && password != null && domain != null;

}

public WindowsIdentity ImpersonationUser
{
get
{
return null;
}
}

#endregion
}

|||Hi,

After much messing about and testing, I finally tracked it down to a problem in the GetformsCredentials function in my Credentials class.

By changing it to the following, everything works.

public bool GetFormsCredentials (out System.Net.Cookie AuthCookie, out string UserName, out string Password, out string Authority)
{
UserName = null;
Password = null;
Authority = null;
AuthCookie = null;

return (false);
}

Thanks

Paul

Logistics - Transaction Log Backup

I rent a dedicated server at a hosting site for a database-driven web
application. I have been doing daily backups of the SQL 2K database (Simple
Recovery method) and then use another service to ship that backup off
somewhere else in the world (can only schedule a backup once a day) in
addition to the daily system backup by the hosting company. Recently, the
server crashed and took with it my RAID drives as they were unreadable
somehow. The data was retrieved but I lost a day's worth of data.
So, I would like to NOT lose a day's worth of data again, and move to a Full
Recovery method with daily full backups with transaction log backups
periodically throughout the day. My trouble is that since I don't have a
dedicated tape machine, or access to a network drive (somewhere far away
from my server), and don't know of any FTP programs (to where?) that would
move those logs off my server, I'm wondering what others do. Just saving
those logs throughout the day won't do any good if the server crashes two
minutes before the backup.
Thanks for any tips on the logistics of backup.For smaller database I do backup locally, then have another job step (or all inside the stored
procedure) that uses the "DOS" COPY command to copy that files over to another machine. If that
isn't an option, I do the backup though an UNC directly to the other machine.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Don Miller" <nospam@.nospam.com> wrote in message news:OxjxSf3cGHA.4900@.TK2MSFTNGP02.phx.gbl...
>I rent a dedicated server at a hosting site for a database-driven web
> application. I have been doing daily backups of the SQL 2K database (Simple
> Recovery method) and then use another service to ship that backup off
> somewhere else in the world (can only schedule a backup once a day) in
> addition to the daily system backup by the hosting company. Recently, the
> server crashed and took with it my RAID drives as they were unreadable
> somehow. The data was retrieved but I lost a day's worth of data.
> So, I would like to NOT lose a day's worth of data again, and move to a Full
> Recovery method with daily full backups with transaction log backups
> periodically throughout the day. My trouble is that since I don't have a
> dedicated tape machine, or access to a network drive (somewhere far away
> from my server), and don't know of any FTP programs (to where?) that would
> move those logs off my server, I'm wondering what others do. Just saving
> those logs throughout the day won't do any good if the server crashes two
> minutes before the backup.
> Thanks for any tips on the logistics of backup.
>|||Just out of curiosity, have you checked with your hosting company about
doing the tape backup for you? Most provide this with your monthly fee
if it's their server.
At any rate, you can write a DTS package to move your backups via FTP
to wherever you like.|||Yes, I've talked to the hosting company (who does the entire system backups
to tape for a monthly fee) and this request (can I use their tape as a
device for backing up log files) seemed to puzzle them.
In the interim today, I found a service called LiveVault that does DB
backups of the "open" .mdf and .ldf files every 15 minutes without
transaction logs or any intervention with "standard" SQL 2K backup methods.
The problem is that this service costs as much as the server per month.
"PSPDBA" <williambr@.state.pa.us> wrote in message
news:1147200884.116152.217410@.i39g2000cwa.googlegroups.com...
> Just out of curiosity, have you checked with your hosting company about
> doing the tape backup for you? Most provide this with your monthly fee
> if it's their server.
> At any rate, you can write a DTS package to move your backups via FTP
> to wherever you like.
>|||You probably wouldn't want to use a tape as a backup device. You are
much better off (quicker) to backup to disk, then transfer to tape.
You can probably just give them the directories you want backed up and
the schedule.
It all comes down to what your downtime costs you - figure that out and
you'll know which avenue is best.

logins/sec

I'm monitoring 2005 while running our application by looking at

sys.dm_os_performance_counters, general statistics, logins/sec.

I began testing a .NET 2 application, and logins/sec went from 63 to 11,433 in 30 minutes. Even though I have closed the application, it is still showing 11,449 - the only thing that's happened in the last hour is that I've run some queries from QA. is this login number cumulative, or is it an actual reflection of logins/sec? How on earth could that many logins be happening when no one else can even get to that server?

Hi,

This counter is indeed cumulative in the dmv.

I would check out the .NET application if it is creating 11000+ logins in 30 minutes.

WesleyB

Visit my SQL Server weblog @. http://dis4ea.blogspot.com

|||Try to log the logins on the server using the logging functionality on the server, logging the failed as well as the successfull logins.

Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

in looking at the event viewer, there are hardly any logon/logout events.

Logging all logins.

?

|||

Here's what I'm seeing over and over in my trace. Our stored procedure should be writing a record each time, but it doesn't come accross in the trace as anything but a read. I have verified that the data is actually getting written to the table.

All spid 62.

All application name = .Net SqlClient Data Provider

EventClass textdata reads writes

Audit Logout NULL 68 4
RPC:Completed exec sp_reset_connection 0 0
Audit Login -- network protocol: TCP/IP set quoted_identifier on set arithabort off set numeric_roundabort off set ansi_warnings on set ansi_padding on set ansi_nulls on set concat_null_yields_null on set cursor_close_on_commit off set implicit_transactions off set language us_english set dateformat mdy set datefirst 7 set transaction isolation level read committed NULL NULL

RPC:Completed exec <<<our stored procedure>>> 2 0
Audit Logout NULL 70 4

RPC:Completed exec sp_reset_connection 0 0
Audit Login -- network protocol: TCP/IP set quoted_identifier on set arithabort off set numeric_roundabort off set ansi_warnings on set ansi_padding on set ansi_nulls on set concat_null_yields_null on set cursor_close_on_commit off set implicit_transactions off set language us_english set dateformat mdy set datefirst 7 set transaction isolation level read committed NULL NULL

RPC:Completed exec <<our stored procedure>> 2 0

Audit Logout NULL 72 4

|||Did you try to implement something like an audit mechanism, to track the written information or execution information in a table ?

Jens K. Suessmeyer.

http://www.sqlserver2005.de
|||not sure what you mean...some kind of trigger?|||Yep.

Jens K. Suessmeyer.

http://www.sqlserver2005.de

Logins and users question

Ok, let me preface my question with a little info about the application I am working on. I am in the middle of creating a project in VB.Net for my company. We have a data management system for handling sampling data. Now the database design is like so. The application is able to handle multiple "Sites". We create a new database for each site that is create dint eh application. These databases are identical in structure but the data is obviously different.

Now we don't actually delete any records in this application rather we mark items as deleted instead. This allows us to easily undo any changes that have been made to the data. When a change or delete are made, we record this change so that reports can be printed to show what changes were made and by who. This is all well and good, but my thoughts are this.

At the moment I am writing lots of VB code to handle adding these records of change and inserting them into the database... What I would like to be able to do is to just create Triggers on the tables to add them. This is something that I know how to do and i feel like it would be the better way to do it except for one thing... here comes the issue...

I have no way of knowing what user is logged in to my application from within the trigger because the application uses a single login for accessing the database. My thoughts are this... Would I be crazy to think that it would be a good idea to create SQL server logins for each user that is created in the application, giving these users access to only the database that they have been created in? This would allow me to determine who was logged in when the change was made and could then implement recording changes through triggers... I am not a very experienced dba programming is more my speciality. I know how to implement this idea, but I am just wondering if this sort of thing is considered bad practice or if it is something I should consider implementing...

Sorry for the novel there and thanks for any help or insight.

BrianNot sure if this helps but there is the suser_sname() which should return the login name (I usually put this in as the default value for my EnteredBy field and I'm not sure if it will work in your case with the single login but it may be worth investigating.)|||Well, that is what I am looking for, but I was wondering if it makes sense to create a SQL server login for each user in my application.|||Well, that is what I am looking for, but I was wondering if it makes sense to create a SQL server login for each user in my application.

It would seem so if you want to track who is inserting or modifying your data!|||We use connection pooling wih a single login to the database

The users have application security, and they log into the app via a user table, with passwrod verification done by an API to RACF

Every database call is limited to Stored Procedures

The Calling App must pass as a parameter to the sproc, the application user that is logged

Any modification that is done records the app user on the row

A trigger then moves the before image row to history as is

The last person making the modification is on the current row

Login/User modification from an application?

I have an application and I want to add/edit/del Logins and Users in Sql
Server 2005 through the application, can I do it? I am using windows
authentication. Currently I call a stored proc that I try to pass a login
and it doesn't work. For example this is my Delete.
ALTER PROCEDURE [dbo].[usp_UserRoleDelete]
-- Add the parameters for the stored procedure here
@.LoginName nvarchar(50)
AS
BEGIN
SET NOCOUNT ON;
Begin
--Drop the login which drops the user?
Drop Login [@.LoginName]
End
END
When it runs I get you can't add a login called @.LoginName. If I take the
brackets off it will not compile. Can i do what I'm trying to do? I've als
o
tried using sp_DropLogin w/o success. I did see somewhere in documentation
for the sp_dropLogin "sp_droplogin cannot be executed within a user-defined
transaction." There is nothing about the in the Drop Login documentation.
Is this the problem?
Thanks in advance,
Greg P.Just to follow up, mainly what I'm trying to do is used passed parameters in
my add/alter/drop statements. Is there a way to have the @.LoginName
evaluated before the the whole line is evaluated?
"Greg P." wrote:

> I have an application and I want to add/edit/del Logins and Users in Sql
> Server 2005 through the application, can I do it? I am using windows
> authentication. Currently I call a stored proc that I try to pass a login
> and it doesn't work. For example this is my Delete.
> ALTER PROCEDURE [dbo].[usp_UserRoleDelete]
> -- Add the parameters for the stored procedure here
> @.LoginName nvarchar(50)
> AS
> BEGIN
> SET NOCOUNT ON;
> Begin
> --Drop the login which drops the user?
> Drop Login [@.LoginName]
> End
> END
> When it runs I get you can't add a login called @.LoginName. If I take the
> brackets off it will not compile. Can i do what I'm trying to do? I've a
lso
> tried using sp_DropLogin w/o success. I did see somewhere in documentatio
n
> for the sp_dropLogin "sp_droplogin cannot be executed within a user-define
d
> transaction." There is nothing about the in the Drop Login documentation.
> Is this the problem?
> Thanks in advance,
> Greg P.|||No...it's because DROP LOGIN itself doesn't accept
parameters and that's what you are asking it to do. You
would need to use dynamic SQL but you really would want to
read the following first:
http://www.sommarskog.se/dynamic_sql.html
So for the stored procedure you posted to work, one option
would be to dynamically build the statement and pass it into
an EXEC.
So instead of the line:
Drop Login [@.LoginName]
you would use something like:
EXEC('DROP LOGIN ' + @.LoginName)
-Sue
On Mon, 25 Sep 2006 13:31:01 -0700, Greg P.
<GregP@.discussions.microsoft.com> wrote:

>I have an application and I want to add/edit/del Logins and Users in Sql
>Server 2005 through the application, can I do it? I am using windows
>authentication. Currently I call a stored proc that I try to pass a login
>and it doesn't work. For example this is my Delete.
>ALTER PROCEDURE [dbo].[usp_UserRoleDelete]
> -- Add the parameters for the stored procedure here
> @.LoginName nvarchar(50)
>AS
>BEGIN
> SET NOCOUNT ON;
> Begin
> --Drop the login which drops the user?
> Drop Login [@.LoginName]
> End
>END
>When it runs I get you can't add a login called @.LoginName. If I take the
>brackets off it will not compile. Can i do what I'm trying to do? I've al
so
>tried using sp_DropLogin w/o success. I did see somewhere in documentation
>for the sp_dropLogin "sp_droplogin cannot be executed within a user-defined
>transaction." There is nothing about the in the Drop Login documentation.
>Is this the problem?
>Thanks in advance,
>Greg P.

Login/Account Lock

I have a scenario in my application that I need to know whether the account
is locked or not before the password is reset. If the account is locked the
app should pop up a msg.
how to programmatically determine that the account is locked or not in sql
server 2005.
Some thing like @.@.islocked or @.@.lock_status or any system SP
Can any one help me as this is an urgent case needed immediate response.
Thanks in advance.
Best regards,
venkateshVenkatesh (Venkatesh@.discussions.microsoft.com) writes:
> I have a scenario in my application that I need to know whether the
> account is locked or not before the password is reset. If the account is
> locked the app should pop up a msg.
> how to programmatically determine that the account is locked or not in sql
> server 2005.
> Some thing like @.@.islocked or @.@.lock_status or any system SP
> Can any one help me as this is an urgent case needed immediate response.
sys.server_principals.is_disabled seems like it could be what you are
asking for.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Hi Erland,
Thanks for the immediate response. But I think this will disable the login -
to disable the login we can very well use the below command
alter login [loginname] disable/enable
But what I want is to determine whether account is locked or not. When you
click the properties of a login in the status tab you would see the account
lock out check box. I want how programatically this can be determined
Correct me If I am wrong. In case if your code
sys.server_principals.is_disabled is used to lock the account could you give
me an example.
"Erland Sommarskog" wrote:

> Venkatesh (Venkatesh@.discussions.microsoft.com) writes:
> sys.server_principals.is_disabled seems like it could be what you are
> asking for.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx
>|||You can use select LOGINPROPERTY('test', N'IsLocked').
Thanks!
--
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCTS: SQL Server 2005|||Venkatesh (Venkatesh@.discussions.microsoft.com) writes:
> Thanks for the immediate response. But I think this will disable the
> login - to disable the login we can very well use the below command
> alter login [loginname] disable/enable
> But what I want is to determine whether account is locked or not. When
> you click the properties of a login in the status tab you would see the
> account lock out check box. I want how programatically this can be
> determined
> Correct me If I am wrong. In case if your code
> sys.server_principals.is_disabled is used to lock the account could you
> give me an example.
OK, I admit that I took a gamble on that one.
It seems that Mohit did a better job on reading the manual that you and I
did.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Login Trigger?

Hi,
I'm looking for something similar to Oracle's login trigger to limit user
connections. I have an application userid that must have read/write/execute
permissions, but other users who should only have read access are logging in
with this user id. This user id is used by a legacy system and no one how
many places the password is hard coded, so changing the password is not an
option.
Is there a way that I can check the hostname of this user on connection, and
if the connection is not coming from an approved host, deny the connection?
Thanks.
SusanI only know a windows trick MIGHT achieve the effect. You can test this: try
setup the sql server's security policy to only allow certain machines to
login. But if a user login from terminal services or citrix client, you need
to think other methods.
James
"Susan Cooper" wrote:
> Hi,
> I'm looking for something similar to Oracle's login trigger to limit user
> connections. I have an application userid that must have read/write/execute
> permissions, but other users who should only have read access are logging in
> with this user id. This user id is used by a legacy system and no one how
> many places the password is hard coded, so changing the password is not an
> option.
> Is there a way that I can check the hostname of this user on connection, and
> if the connection is not coming from an approved host, deny the connection?
> Thanks.
> Susan

Login Trigger?

Hi,
I'm looking for something similar to Oracle's login trigger to limit user
connections. I have an application userid that must have read/write/execute
permissions, but other users who should only have read access are logging in
with this user id. This user id is used by a legacy system and no one how
many places the password is hard coded, so changing the password is not an
option.
Is there a way that I can check the hostname of this user on connection, and
if the connection is not coming from an approved host, deny the connection?
Thanks.
Susan
I only know a windows trick MIGHT achieve the effect. You can test this: try
setup the sql server's security policy to only allow certain machines to
login. But if a user login from terminal services or citrix client, you need
to think other methods.
James
"Susan Cooper" wrote:

> Hi,
> I'm looking for something similar to Oracle's login trigger to limit user
> connections. I have an application userid that must have read/write/execute
> permissions, but other users who should only have read access are logging in
> with this user id. This user id is used by a legacy system and no one how
> many places the password is hard coded, so changing the password is not an
> option.
> Is there a way that I can check the hostname of this user on connection, and
> if the connection is not coming from an approved host, deny the connection?
> Thanks.
> Susan

Login Trigger?

Hi,
I'm looking for something similar to Oracle's login trigger to limit user
connections. I have an application userid that must have read/write/execute
permissions, but other users who should only have read access are logging in
with this user id. This user id is used by a legacy system and no one how
many places the password is hard coded, so changing the password is not an
option.
Is there a way that I can check the hostname of this user on connection, and
if the connection is not coming from an approved host, deny the connection?
Thanks.
SusanI only know a windows trick MIGHT achieve the effect. You can test this: try
setup the sql server's security policy to only allow certain machines to
login. But if a user login from terminal services or citrix client, you need
to think other methods.
James
"Susan Cooper" wrote:

> Hi,
> I'm looking for something similar to Oracle's login trigger to limit user
> connections. I have an application userid that must have read/write/execu
te
> permissions, but other users who should only have read access are logging
in
> with this user id. This user id is used by a legacy system and no one how
> many places the password is hard coded, so changing the password is not an
> option.
> Is there a way that I can check the hostname of this user on connection, a
nd
> if the connection is not coming from an approved host, deny the connection
?
> Thanks.
> Susan

Login to SQL Server for Web Application

Hello everyone,

I am currently developing a web application that retrieves and updates contact information in a database. I am coding the application with VB.Net and ASP.Net with a little bit of javascript. It is accessing a SQL Server Database.

Everything is working fine when I run the web application from within the VS.Net environment. However after publishing the website to our local Intranet (where the app will be stored) I get the following error:

Server Error in '/ollodev1/RIC' Application.

---

Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection.

Description:Anunhandled exception occurred during the execution of the current webrequest. Please review the stack trace for more information about theerror and where it originated in the code.

Exception Details:System.Data.SqlClient.SqlException: Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection.

Source Error:

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

I assume that this message appears because I am not retrieving setting the proper access rights from the DB but I don't know how to go about doing this. Could someone provide me with a link to a few examples to help resolve my issue?

Thanks,

Eric

Also, I've been testing to resolve the matter and I assume it is because of this.

When I added the line of: label1.text = Request.Servervariables("AUTH_USER")

it shows: domain1\<winnt id>

But when I published the website to our intranet:

The label is blanked...

I'm trying to add as much info as possible.

Thanks again

|||

The Null user error is related to your SQL Server installed with Windows Authentication and your application connecting to SQL Server with SQL Server authentication, you can solve this by changing SQL Server to mixed authentication. That is covered in the link below.

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

Asp.net account in IIS5 that is Win2k and XP, and Network service account in Win2003 needs permissions in SQL Server, there are two permissions in SQL Server the server permissions in the security section under management in Management Studio and the database permissions in the new security section within the database in Management Studio. When you have finished creating the permissions adjust your Web.Config as in the link below. Hope this helps.

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

login security question

Hi,
I have an application which connects to the SQL server. We have several users logging into this application. All of their user-id, passwords are validated and converted to an owner profile, which is then used throughout the application.
My problem is, this owner profile should be prevented from accessing the database directly using Enterprise Manager or Query Analyser. The database should be accessible only from the application for this owner/global profile.
How do I go about achieving this. The application was set up like this by a person long time back who is not with us anymore. Also, I do not know SQL Server Administration. So, please detail out what information I have to look up and what steps I will hav
e to follow.
Thank you in advance.
Sunny
Look into SQL Server Books online for "application roles" topic. This
explains about how to create and activate an application role within your
program and use it. This may require some code changes.
HTH
Prasad Koukuntla
"Sunanda" <Sunny@.discussions.microsoft.com> wrote in message
news:87CB3205-C041-4F23-AC6E-7BF23E7AB2C6@.microsoft.com...
> Hi,
> I have an application which connects to the SQL server. We have several
users logging into this application. All of their user-id, passwords are
validated and converted to an owner profile, which is then used throughout
the application.
> My problem is, this owner profile should be prevented from accessing the
database directly using Enterprise Manager or Query Analyser. The database
should be accessible only from the application for this owner/global
profile.
> How do I go about achieving this. The application was set up like this by
a person long time back who is not with us anymore. Also, I do not know SQL
Server Administration. So, please detail out what information I have to look
up and what steps I will have to follow.
> Thank you in advance.
> Sunny
|||In my opinion, that does not seem possible. SQL doesn't know what CLIENT TOOL is touching it. If the "connection" from the client application comes in through a username/password, then that username/password has access to SELECT, UPDATE, DELETE, etc fro
m tables.
That is why we do all our database access through STORED PROCEDURES - so actual table access is not possible. Granted, the users can still call STORED PROCEDURES from the EM and QA tools, but that is less likely to happen.
Can you hide the "connection" username/password from the users?
"Sunanda" wrote:

> Hi,
> I have an application which connects to the SQL server. We have several users logging into this application. All of their user-id, passwords are validated and converted to an owner profile, which is then used throughout the application.
> My problem is, this owner profile should be prevented from accessing the database directly using Enterprise Manager or Query Analyser. The database should be accessible only from the application for this owner/global profile.
> How do I go about achieving this. The application was set up like this by a person long time back who is not with us anymore. Also, I do not know SQL Server Administration. So, please detail out what information I have to look up and what steps I will h
ave to follow.
> Thank you in advance.
> Sunny
|||Steve,
No the connection profile is alreay know to the users, that is why we would like to prevent users from using that in the enterprise manager to make changes.
Please let me know if there are any options.
Thanks a lot.
Sunny
"Steve Z" wrote:

> In my opinion, that does not seem possible. SQL doesn't know what CLIENT TOOL is touching it. If the "connection" from the client application comes in through a username/password, then that username/password has access to SELECT, UPDATE, DELETE, etc f
rom tables.[vbcol=seagreen]
> That is why we do all our database access through STORED PROCEDURES - so actual table access is not possible. Granted, the users can still call STORED PROCEDURES from the EM and QA tools, but that is less likely to happen.
> Can you hide the "connection" username/password from the users?
> "Sunanda" wrote:
have to follow.[vbcol=seagreen]
|||"Sunanda" <Sunny@.discussions.microsoft.com> wrote in message
news:87CB3205-C041-4F23-AC6E-7BF23E7AB2C6@.microsoft.com...
> Hi,
> I have an application which connects to the SQL server. We have several
users logging into this application. All of their user-id, passwords are
validated and converted to an owner profile, which is then used throughout
the application.
> My problem is, this owner profile should be prevented from accessing the
database directly using Enterprise Manager or Query Analyser. The database
should be accessible only from the application for this owner/global
profile.
> How do I go about achieving this. The application was set up like this by
a person long time back who is not with us anymore. Also, I do not know SQL
Server Administration. So, please detail out what information I have to look
up and what steps I will have to follow.
If you can alter the code in the client application, you can use application
roles.
1) Use Enterprise Manager to access the database / roles. New Role. click
the Application Role radio button and give it a nice secure, obscure
password.
2) Give the Application Role the appropriate permissions.
3) Revoke the users' permissions
4) in the code of the application, put in a call to a stored procedure
called (I think, from memory) sp_setAppRole (F1 for application role to see
what the stored proc is called) using the secret password for the App Role
(which you don't share with the end users).
Now your users will have the appropriate permissions when using your app,
but not when using QA or any other app.
On an entirely different tack, you can try Group Policies. Use a GP to tie
down their desktop so that they are not allowed to run Quey Analyzer or
Enterprise Manager.
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.711 / Virus Database: 467 - Release Date: 25/06/2004
|||Bob,
Thanks for your reply. I kinda understand this Application Role approach. Could you please explain the following:
1. Say the password for the Application role is found, can a user access the database through the Query Analyser or Enterprise Manager using the application rolde/password.
2. At present the application tracks the user who makes the changes to the database through the application. The application passes the userid to the stored procedures. But if I put in a Applciation role in between, will I still have the actual userid to
track who actually did the inserts and updates through the front-end.
Thanks in advance,
Sunanda.
"Bob Simms" wrote:

> "Sunanda" <Sunny@.discussions.microsoft.com> wrote in message
> news:87CB3205-C041-4F23-AC6E-7BF23E7AB2C6@.microsoft.com...
> users logging into this application. All of their user-id, passwords are
> validated and converted to an owner profile, which is then used throughout
> the application.
> database directly using Enterprise Manager or Query Analyser. The database
> should be accessible only from the application for this owner/global
> profile.
> a person long time back who is not with us anymore. Also, I do not know SQL
> Server Administration. So, please detail out what information I have to look
> up and what steps I will have to follow.
> If you can alter the code in the client application, you can use application
> roles.
> 1) Use Enterprise Manager to access the database / roles. New Role. click
> the Application Role radio button and give it a nice secure, obscure
> password.
> 2) Give the Application Role the appropriate permissions.
> 3) Revoke the users' permissions
> 4) in the code of the application, put in a call to a stored procedure
> called (I think, from memory) sp_setAppRole (F1 for application role to see
> what the stored proc is called) using the secret password for the App Role
> (which you don't share with the end users).
> Now your users will have the appropriate permissions when using your app,
> but not when using QA or any other app.
> On an entirely different tack, you can try Group Policies. Use a GP to tie
> down their desktop so that they are not allowed to run Quey Analyzer or
> Enterprise Manager.
>
> --
> Outgoing mail is certified Virus Free.
> Checked by AVG anti-virus system (http://www.grisoft.com).
> Version: 6.0.711 / Virus Database: 467 - Release Date: 25/06/2004
>
>
|||<<1. Say the password for the Application role is found, can a user access the database through the
Query Analyser or Enterprise Manager using the application rolde/password.>>
Yes.
<<2. At present the application tracks the user who makes the changes to the database through the
application. The application passes the userid to the stored procedures. But if I put in a
Applciation role in between, will I still have the actual userid to track who actually did the
inserts and updates through the front-end.>>
Yes. You can see the login id for the users, and you can use the SYSTEM_USER function in, for
example, a trigger to get the login name. The user name, however, will be the app role name.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Sunanda" <Sunanda@.discussions.microsoft.com> wrote in message
news:00D6F5D5-0BC3-43A4-A313-649B5E2E215C@.microsoft.com...
> Bob,
> Thanks for your reply. I kinda understand this Application Role approach. Could you please explain
the following:
> 1. Say the password for the Application role is found, can a user access the database through the
Query Analyser or Enterprise Manager using the application rolde/password.
> 2. At present the application tracks the user who makes the changes to the database through the
application. The application passes the userid to the stored procedures. But if I put in a
Applciation role in between, will I still have the actual userid to track who actually did the
inserts and updates through the front-end.[vbcol=seagreen]
> Thanks in advance,
> Sunanda.
>
> "Bob Simms" wrote:
|||Thanks for your reply.
But can't the application role be restricted from using the QA/EM? This is not a completely secure method. Is there any alternative.
Thanks,
sunanda.
"Tibor Karaszi" wrote:

> <<1. Say the password for the Application role is found, can a user access the database through the
> Query Analyser or Enterprise Manager using the application rolde/password.>>
> Yes.
>
> <<2. At present the application tracks the user who makes the changes to the database through the
> application. The application passes the userid to the stored procedures. But if I put in a
> Applciation role in between, will I still have the actual userid to track who actually did the
> inserts and updates through the front-end.>>
> Yes. You can see the login id for the users, and you can use the SYSTEM_USER function in, for
> example, a trigger to get the login name. The user name, however, will be the app role name.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Sunanda" <Sunanda@.discussions.microsoft.com> wrote in message
> news:00D6F5D5-0BC3-43A4-A313-649B5E2E215C@.microsoft.com...
> the following:
> Query Analyser or Enterprise Manager using the application rolde/password.
> application. The application passes the userid to the stored procedures. But if I put in a
> Applciation role in between, will I still have the actual userid to track who actually did the
> inserts and updates through the front-end.
>
>
|||> But can't the application role be restricted from using the QA/EM?
No, that is not the way it work. You need to protect the password. Why do you say it is not a secure method?
Are you afraid of network sniffing? There's an encryption option in sp_setapprole.

> Is there any alternative.
I don't know what your requirements are, as I haven't read the full thread. App roles is a nice feature for
what it is performing. Other options includes app logins using a special password (but all users will use the
same logins), app uses stored procedures and views to access data...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Sunanda" <Sunanda@.discussions.microsoft.com> wrote in message
news:CA998A64-A51E-42B4-9B8D-E06C37624B65@.microsoft.com...
> Thanks for your reply.
> But can't the application role be restricted from using the QA/EM? This is not a completely secure method.
Is there any alternative.[vbcol=seagreen]
> Thanks,
> sunanda.
>
> "Tibor Karaszi" wrote: