BUG: SECURITY Problem 1355 error

Hello
I am receiving the following error for subscriptions we create:
ReportingServicesService!library!c24!10/22/2004-21:27:04:: e ERROR: Throwing
Microsoft.ReportingServices.Diagnostics.Utilities.ServerConfigurationErrorException:
The Report Server has encountered a configuration error; more details in the
log files, AuthzInitializeContextFromSid: Win32 error: 1355;
Info:
Microsoft.ReportingServices.Diagnostics.Utilities.ServerConfigurationErrorException:
The Report Server has encountered a configuration error; more details in the
log files
IMPORTANT: These subscriptions were working last week but after applying
ALL the outsanding Windows 2003 patches the problem showed up. Also, it
doesn't matter which delivery extension (including custom) we choose.. we get
the same 1355 error. We can run the reports interactively through report
manager so the issue is definetly subscription related.
We are running the following software:
- Windows 2003 including ALL patches released to date (latest monthly
patches appear to be causing this issue)
- SQL Server 2000 SP3a
- SQL Reporting Services Standard Addition SP1
- Exchange 2000. I only mention this as other posts have suggested this is
Lotus notes issue. In our case it doesn't matter which delivery extension we
choose.
I can get the subscriptions to work using a god like account (ie has domain
admin rights) so the issue appears to center around which user account
actually owns the subscription. If I modify the subscription with any other
account the subscription continues to function properly.
My question is how is the OwnerID found in Subscription table actualy used
becuase this appear to be the key to resolving this issue?
Regards
Kevin WeirA follow-up to my own post:
Here is sample eror log messages containing error:
ReportingServicesService!dbpolling!c24!10/22/2004-21:27:03:: EventPolling
processing item cb787fc0-b06b-4bc0-aa65-386991109995
ReportingServicesService!dbpolling!7dc!10/22/2004-21:27:03:: EventPolling
processing 1 more items. 1 Total items in internal queue.
ReportingServicesService!library!c24!10/22/2004-21:27:03:: Schedule
90015ab7-ec96-4891-b51f-c3ff202da46b executed at 10/22/2004 9:27:03 PM.
ReportingServicesService!schedule!c24!10/22/2004-21:27:03:: Creating Time
based subscription notification for subscription:
bb790a12-151c-4074-a79b-61c158088965
ReportingServicesService!library!c24!10/22/2004-21:27:04:: Schedule
90015ab7-ec96-4891-b51f-c3ff202da46b execution completed at 10/22/2004
9:27:04 PM.
ReportingServicesService!dbpolling!c24!10/22/2004-21:27:04:: EventPolling
finished processing item cb787fc0-b06b-4bc0-aa65-386991109995
ReportingServicesService!dbpolling!c24!10/22/2004-21:27:04::
NotificationPolling processing item a7712007-71d4-44cc-81c0-5a68b9bbdf6a
ReportingServicesService!dbpolling!7dc!10/22/2004-21:27:04::
NotificationPolling processing 1 more items. 1 Total items in internal queue.
ReportingServicesService!library!c24!10/22/2004-21:27:04:: i INFO: Call to
RenderFirst( '/Test' )
ReportingServicesService!library!c24!10/22/2004-21:27:04:: e ERROR: Throwing
Microsoft.ReportingServices.Diagnostics.Utilities.ServerConfigurationErrorException:
The Report Server has encountered a configuration error; more details in the
log files, AuthzInitializeContextFromSid: Win32 error: 1355;
Info:
Microsoft.ReportingServices.Diagnostics.Utilities.ServerConfigurationErrorException:
The Report Server has encountered a configuration error; more details in the
log files
ReportingServicesService!library!c24!10/22/2004-21:27:04:: i INFO:
Initializing EnableExecutionLogging to 'True' as specified in Server system
properties.
ReportingServicesService!emailextension!c24!10/22/2004-21:27:04:: Error
sending email. Microsoft.ReportingServices.Diagnostics.Utilities.RSException:
The Report Server has encountered a configuration error; more details in the
log files -->
Microsoft.ReportingServices.Diagnostics.Utilities.ServerConfigurationErrorException:
The Report Server has encountered a configuration error; more details in the
log files
at
Microsoft.ReportingServices.Authorization.Native.GetAuthzContextForUser(IntPtr userSid)
at Microsoft.ReportingServices.Authorization.Native.IsAdmin(String
userName)
at
Microsoft.ReportingServices.Authorization.WindowsAuthorization.IsAdmin(String
userName, IntPtr userToken)
at
Microsoft.ReportingServices.Authorization.WindowsAuthorization.CheckAccess(String
userName, IntPtr userToken, Byte[] secDesc, ReportOperation requiredOperation)
at Microsoft.ReportingServices.Library.Security.CheckAccess(ItemType
catItemType, Byte[] secDesc, ReportOperation rptOper)
at
Microsoft.ReportingServices.Library.RSService._GetReportParameterDefinitionFromCatalog(CatalogItemContext
reportContext, String historyID, Boolean forRendering, Guid& reportID, Int32&
executionOption, String& savedParametersXml, ReportSnapshot&
compiledDefinition, ReportSnapshot& snapshotData, Guid& linkID, DateTime&
historyDate)
at
Microsoft.ReportingServices.Library.RSService._GetReportParameters(String
report, String historyID, Boolean forRendering, NameValueCollection values,
DatasourceCredentialsCollection credentials)
at
Microsoft.ReportingServices.Library.RSService.RenderAsLiveOrSnapshot(CatalogItemContext
reportContext, ClientRequest session, Warning[]& warnings,
ParameterInfoCollection& effectiveParameters)
at
Microsoft.ReportingServices.Library.RSService.RenderFirst(CatalogItemContext
reportContext, ClientRequest session, Warning[]& warnings,
ParameterInfoCollection& effectiveParameters, String[]& secondaryStreamNames)
at Microsoft.ReportingServices.Library.RenderFirstCancelableStep.Execute()
at
Microsoft.ReportingServices.Diagnostics.CancelablePhaseBase.ExecuteWrapper()
-- End of inner exception stack trace --
at
Microsoft.ReportingServices.Diagnostics.CancelablePhaseBase.ExecuteWrapper()
at
Microsoft.ReportingServices.Library.RenderFirstCancelableStep.RenderFirst(RSService
rs, CatalogItemContext reportContext, ClientRequest session, JobTypeEnum
type, Warning[]& warnings, ParameterInfoCollection& effectiveParameters,
String[]& secondaryStreamNames)
at Microsoft.ReportingServices.Library.ReportImpl.Render(String
renderFormat, String deviceInfo)
at
Microsoft.ReportingServices.EmailDeliveryProvider.EmailProvider.ConstructMessageBody(IMessage message, Notification notification, SubscriptionData data)
at
Microsoft.ReportingServices.EmailDeliveryProvider.EmailProvider.CreateMessage(Notification notification)
at
Microsoft.ReportingServices.EmailDeliveryProvider.EmailProvider.Deliver(Notification notification)
ReportingServicesService!notification!c24!10/22/2004-21:27:04:: Notification
a7712007-71d4-44cc-81c0-5a68b9bbdf6a completed. Success: False, Status:
Failure sending mail: The Report Server has encountered a configuration
error; more details in the log files, DeliveryExtension: Report Server Email,
Report: Test, Attempt 0
ReportingServicesService!dbpolling!c24!10/22/2004-21:27:04::
NotificationPolling finished processing item
a7712007-71d4-44cc-81c0-5a68b9bbdf6a
ReportingServicesService!library!c24!10/22/2004-21:27:47:: i INFO: Cleaned 0
batch records, 0 policies, 0 sessions, 0 cache entries, 0 snapshots, 0
chunks, 0 running jobs|||If anyone else reads this and has same problem I applied Windows 2003 hotfix
834859 which resolved the problem
http://support.microsoft.com/default.aspx?scid=kb;en-us;834859
"Kevin Weir" wrote:
> Hello
> I am receiving the following error for subscriptions we create:
> ReportingServicesService!library!c24!10/22/2004-21:27:04:: e ERROR: Throwing
> Microsoft.ReportingServices.Diagnostics.Utilities.ServerConfigurationErrorException:
> The Report Server has encountered a configuration error; more details in the
> log files, AuthzInitializeContextFromSid: Win32 error: 1355;
> Info:
> Microsoft.ReportingServices.Diagnostics.Utilities.ServerConfigurationErrorException:
> The Report Server has encountered a configuration error; more details in the
> log files
> IMPORTANT: These subscriptions were working last week but after applying
> ALL the outsanding Windows 2003 patches the problem showed up. Also, it
> doesn't matter which delivery extension (including custom) we choose.. we get
> the same 1355 error. We can run the reports interactively through report
> manager so the issue is definetly subscription related.
> We are running the following software:
> - Windows 2003 including ALL patches released to date (latest monthly
> patches appear to be causing this issue)
> - SQL Server 2000 SP3a
> - SQL Reporting Services Standard Addition SP1
> - Exchange 2000. I only mention this as other posts have suggested this is
> Lotus notes issue. In our case it doesn't matter which delivery extension we
> choose.
> I can get the subscriptions to work using a god like account (ie has domain
> admin rights) so the issue appears to center around which user account
> actually owns the subscription. If I modify the subscription with any other
> account the subscription continues to function properly.
> My question is how is the OwnerID found in Subscription table actualy used
> becuase this appear to be the key to resolving this issue?
> Regards
> Kevin Weir
>

BUG: Reporting Services Custom Security and Subscriptions?

Basically we have implemented custom security so that our logins to the
Report Server are using details in a custom database. They are not Windows
usernames/passwords. However when we use the web service api
(ListSubscriptions) to return user's subscriptions we get nothing? After
digging further with Reflector I found the following code in an RS assembly:
public ArrayList ListSubscriptions(string user, string report)
{
ArrayList list1 = new ArrayList();
string text1 = "select
S.[SubscriptionID],
S.[Report_OID],
S.[Locale],
S.[InactiveFlags],
S.[DeliveryExtension],
S.[ExtensionSettings],
SUSER_SNAME(Modified.[Sid]),
Modified.[UserName],
S.[ModifiedDate],
S.[Description],
S.[LastStatus],
S.[EventType],
S.[MatchData],
S.[Parameters],
S.[DataSettings],
A.[TotalNotifications],
A.[TotalSuccesses],
A.[TotalFailures],
SUSER_SNAME(Owner.[Sid]),
Owner.[UserName],
CAT.[Path],
S.[LastRunTime],
CAT.[Type],
SD.NtSecDescPrimary
from
[Subscriptions] S inner join [Catalog] CAT on S.[Report_OID] = CAT.[ItemID]
inner join [Users] Owner on S.OwnerID = Owner.UserID
inner join [Users] Modified on S.ModifiedByID = Modified.UserID
left outer join [SecData] SD on CAT.[PolicyID] = SD.[PolicyID]
AND SD.AuthType = @.AuthType
left outer join [ActiveSubscriptions] A with (NOLOCK) on
S.[SubscriptionID] = A.[SubscriptionID]";
InstrumentedSqlCommand command1 = Storage.NewSqlCommand(text1,
CommandType.Text, base.Connection, base.Transaction,
base.SqlCommandTimeout);
command1.Parameters.Add("@.AuthType",
WebConfigUtil.AuthenticationType);
if ((user != null) && (user != ""))
{
this.AddClause(ref text1, ref flag1, "Owner.[Sid] = @.UserSid");
command1.Parameters.Add("@.UserSid", Native.NameToSid(user));
}
<removed code>
}
Now the interesting bit is:
this.AddClause(ref text1, ref flag1, "Owner.[Sid] = @.UserSid");
command1.Parameters.Add("@.UserSid", Native.NameToSid(user));
Why are they trying to lookup a SID for my custom username? Of course this
fails, which results in a WHERE clause that fails to grabs our
subscriptions.
Has anyone else found this? Am I completely wrong?
BTW: I'm using Reporting Services SP 2.
James Snape (for Ryan Stevens)Hello James,
Based on my scope, if a report server user is not mapped to a Windows user
sid, it shall be NULL. I think this shall work properly in SQL query.
select * from users where sid=NULL
Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
--
| From: "James Snape" <jim_snape.at.hotmail.com@.online.nospam>
| Subject: BUG: Reporting Services Custom Security and Subscriptions?
| Date: Fri, 7 Oct 2005 09:25:13 +0100
| Lines: 75
| X-Priority: 3
| X-MSMail-Priority: Normal
| X-Newsreader: Microsoft Outlook Express 6.00.2900.2670
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2670
| X-RFC2646: Format=Flowed; Original
| Message-ID: <OH8poixyFHA.2540@.TK2MSFTNGP09.phx.gbl>
| Newsgroups: microsoft.public.sqlserver.reportingsvcs
| NNTP-Posting-Host: ip-213-92-131-1.aramiska-arc.aramiska.net 213.92.131.1
| Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP09.phx.gbl
| Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.reportingsvcs:53918
| X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
|
| Basically we have implemented custom security so that our logins to the
| Report Server are using details in a custom database. They are not
Windows
| usernames/passwords. However when we use the web service api
| (ListSubscriptions) to return user's subscriptions we get nothing? After
| digging further with Reflector I found the following code in an RS
assembly:
|
| public ArrayList ListSubscriptions(string user, string report)
| {
| ArrayList list1 = new ArrayList();
| string text1 = "select
| S.[SubscriptionID],
| S.[Report_OID],
| S.[Locale],
| S.[InactiveFlags],
| S.[DeliveryExtension],
| S.[ExtensionSettings],
| SUSER_SNAME(Modified.[Sid]),
| Modified.[UserName],
| S.[ModifiedDate],
| S.[Description],
| S.[LastStatus],
| S.[EventType],
| S.[MatchData],
| S.[Parameters],
| S.[DataSettings],
| A.[TotalNotifications],
| A.[TotalSuccesses],
| A.[TotalFailures],
| SUSER_SNAME(Owner.[Sid]),
| Owner.[UserName],
| CAT.[Path],
| S.[LastRunTime],
| CAT.[Type],
| SD.NtSecDescPrimary
| from
| [Subscriptions] S inner join [Catalog] CAT on S.[Report_OID]
=| CAT.[ItemID]
| inner join [Users] Owner on S.OwnerID = Owner.UserID
| inner join [Users] Modified on S.ModifiedByID =Modified.UserID
| left outer join [SecData] SD on CAT.[PolicyID] =SD.[PolicyID]
| AND SD.AuthType = @.AuthType
| left outer join [ActiveSubscriptions] A with (NOLOCK) on
| S.[SubscriptionID] = A.[SubscriptionID]";
|
| InstrumentedSqlCommand command1 = Storage.NewSqlCommand(text1,
| CommandType.Text, base.Connection, base.Transaction,
| base.SqlCommandTimeout);
| command1.Parameters.Add("@.AuthType",
| WebConfigUtil.AuthenticationType);
| if ((user != null) && (user != ""))
| {
| this.AddClause(ref text1, ref flag1, "Owner.[Sid] =@.UserSid");
| command1.Parameters.Add("@.UserSid", Native.NameToSid(user));
| }
|
| <removed code>
| }
|
| Now the interesting bit is:
|
| this.AddClause(ref text1, ref flag1, "Owner.[Sid] =@.UserSid");
| command1.Parameters.Add("@.UserSid", Native.NameToSid(user));
|
|
| Why are they trying to lookup a SID for my custom username? Of course
this
| fails, which results in a WHERE clause that fails to grabs our
| subscriptions.
|
| Has anyone else found this? Am I completely wrong?
|
| BTW: I'm using Reporting Services SP 2.
|
| James Snape (for Ryan Stevens)
|
|
||||Hi Peter,
A comparison against NULL is always false so your query below never returns
any results.
Regards,
James Snape
"Peter Yang [MSFT]" <petery@.online.microsoft.com> wrote in message
news:te%23MlbyyFHA.768@.TK2MSFTNGXA01.phx.gbl...
> Hello James,
> Based on my scope, if a report server user is not mapped to a Windows user
> sid, it shall be NULL. I think this shall work properly in SQL query.
> select * from users where sid=NULL
> Regards,
> Peter Yang
> MCSE2000/2003, MCSA, MCDBA
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> =====================================================>
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> --
> | From: "James Snape" <jim_snape.at.hotmail.com@.online.nospam>
> | Subject: BUG: Reporting Services Custom Security and Subscriptions?
> | Date: Fri, 7 Oct 2005 09:25:13 +0100
> | Lines: 75
> | X-Priority: 3
> | X-MSMail-Priority: Normal
> | X-Newsreader: Microsoft Outlook Express 6.00.2900.2670
> | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2670
> | X-RFC2646: Format=Flowed; Original
> | Message-ID: <OH8poixyFHA.2540@.TK2MSFTNGP09.phx.gbl>
> | Newsgroups: microsoft.public.sqlserver.reportingsvcs
> | NNTP-Posting-Host: ip-213-92-131-1.aramiska-arc.aramiska.net
> 213.92.131.1
> | Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP09.phx.gbl
> | Xref: TK2MSFTNGXA01.phx.gbl
> microsoft.public.sqlserver.reportingsvcs:53918
> | X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
> |
> | Basically we have implemented custom security so that our logins to the
> | Report Server are using details in a custom database. They are not
> Windows
> | usernames/passwords. However when we use the web service api
> | (ListSubscriptions) to return user's subscriptions we get nothing? After
> | digging further with Reflector I found the following code in an RS
> assembly:
> |
> | public ArrayList ListSubscriptions(string user, string report)
> | {
> | ArrayList list1 = new ArrayList();
> | string text1 = "select
> | S.[SubscriptionID],
> | S.[Report_OID],
> | S.[Locale],
> | S.[InactiveFlags],
> | S.[DeliveryExtension],
> | S.[ExtensionSettings],
> | SUSER_SNAME(Modified.[Sid]),
> | Modified.[UserName],
> | S.[ModifiedDate],
> | S.[Description],
> | S.[LastStatus],
> | S.[EventType],
> | S.[MatchData],
> | S.[Parameters],
> | S.[DataSettings],
> | A.[TotalNotifications],
> | A.[TotalSuccesses],
> | A.[TotalFailures],
> | SUSER_SNAME(Owner.[Sid]),
> | Owner.[UserName],
> | CAT.[Path],
> | S.[LastRunTime],
> | CAT.[Type],
> | SD.NtSecDescPrimary
> | from
> | [Subscriptions] S inner join [Catalog] CAT on
> S.[Report_OID]
> => | CAT.[ItemID]
> | inner join [Users] Owner on S.OwnerID = Owner.UserID
> | inner join [Users] Modified on S.ModifiedByID => Modified.UserID
> | left outer join [SecData] SD on CAT.[PolicyID] => SD.[PolicyID]
> | AND SD.AuthType = @.AuthType
> | left outer join [ActiveSubscriptions] A with (NOLOCK) on
> | S.[SubscriptionID] = A.[SubscriptionID]";
> |
> | InstrumentedSqlCommand command1 = Storage.NewSqlCommand(text1,
> | CommandType.Text, base.Connection, base.Transaction,
> | base.SqlCommandTimeout);
> | command1.Parameters.Add("@.AuthType",
> | WebConfigUtil.AuthenticationType);
> | if ((user != null) && (user != ""))
> | {
> | this.AddClause(ref text1, ref flag1, "Owner.[Sid] => @.UserSid");
> | command1.Parameters.Add("@.UserSid", Native.NameToSid(user));
> | }
> |
> | <removed code>
> | }
> |
> | Now the interesting bit is:
> |
> | this.AddClause(ref text1, ref flag1, "Owner.[Sid] => @.UserSid");
> | command1.Parameters.Add("@.UserSid", Native.NameToSid(user));
> |
> |
> | Why are they trying to lookup a SID for my custom username? Of course
> this
> | fails, which results in a WHERE clause that fails to grabs our
> | subscriptions.
> |
> | Has anyone else found this? Am I completely wrong?
> |
> | BTW: I'm using Reporting Services SP 2.
> |
> | James Snape (for Ryan Stevens)
> |
> |
> |
>|||Hello James,
If ANSI_NULLS is off, it shall return all users with NULL SID. I have
reported this issue to the proper channel but there is no feedback yet. If
we have any update on this, we will let you know.
Also, since the issue relates to source code evaluation, I recommend that
you open a Support incident with Microsoft Product Support Services so that
a dedicated Support Professional can assist with this case. If you need any
help in this regard, please let me know.
For a complete list of Microsoft Product Support Services phone numbers,
please go to the following address on the World Wide Web:
http://support.microsoft.com/directory/overview.asp
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
| From: "James Snape" <jim_snape.at.hotmail.com@.online.nospam>
| References: <OH8poixyFHA.2540@.TK2MSFTNGP09.phx.gbl>
<te#MlbyyFHA.768@.TK2MSFTNGXA01.phx.gbl>
| Subject: Re: BUG: Reporting Services Custom Security and Subscriptions?
| Date: Fri, 7 Oct 2005 12:23:31 +0100
| Lines: 140
| X-Priority: 3
| X-MSMail-Priority: Normal
| X-Newsreader: Microsoft Outlook Express 6.00.2900.2670
| X-RFC2646: Format=Flowed; Original
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2670
| Message-ID: <uBkwSGzyFHA.2644@.TK2MSFTNGP09.phx.gbl>
| Newsgroups: microsoft.public.sqlserver.reportingsvcs
| NNTP-Posting-Host: ip-213-92-131-1.aramiska-arc.aramiska.net 213.92.131.1
| Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP09.phx.gbl
| Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.reportingsvcs:53926
| X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
|
| Hi Peter,
|
| A comparison against NULL is always false so your query below never
returns
| any results.
|
| Regards,
| James Snape
|
| "Peter Yang [MSFT]" <petery@.online.microsoft.com> wrote in message
| news:te%23MlbyyFHA.768@.TK2MSFTNGXA01.phx.gbl...
| > Hello James,
| >
| > Based on my scope, if a report server user is not mapped to a Windows
user
| > sid, it shall be NULL. I think this shall work properly in SQL query.
| >
| > select * from users where sid=NULL
| >
| > Regards,
| >
| > Peter Yang
| > MCSE2000/2003, MCSA, MCDBA
| > Microsoft Online Partner Support
| >
| > When responding to posts, please "Reply to Group" via your newsreader so
| > that others may learn and benefit from your issue.
| >
| > =====================================================| >
| >
| >
| > This posting is provided "AS IS" with no warranties, and confers no
| > rights.
| >
| > --
| > | From: "James Snape" <jim_snape.at.hotmail.com@.online.nospam>
| > | Subject: BUG: Reporting Services Custom Security and Subscriptions?
| > | Date: Fri, 7 Oct 2005 09:25:13 +0100
| > | Lines: 75
| > | X-Priority: 3
| > | X-MSMail-Priority: Normal
| > | X-Newsreader: Microsoft Outlook Express 6.00.2900.2670
| > | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2670
| > | X-RFC2646: Format=Flowed; Original
| > | Message-ID: <OH8poixyFHA.2540@.TK2MSFTNGP09.phx.gbl>
| > | Newsgroups: microsoft.public.sqlserver.reportingsvcs
| > | NNTP-Posting-Host: ip-213-92-131-1.aramiska-arc.aramiska.net
| > 213.92.131.1
| > | Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP09.phx.gbl
| > | Xref: TK2MSFTNGXA01.phx.gbl
| > microsoft.public.sqlserver.reportingsvcs:53918
| > | X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
| > |
| > | Basically we have implemented custom security so that our logins to
the
| > | Report Server are using details in a custom database. They are not
| > Windows
| > | usernames/passwords. However when we use the web service api
| > | (ListSubscriptions) to return user's subscriptions we get nothing?
After
| > | digging further with Reflector I found the following code in an RS
| > assembly:
| > |
| > | public ArrayList ListSubscriptions(string user, string report)
| > | {
| > | ArrayList list1 = new ArrayList();
| > | string text1 = "select
| > | S.[SubscriptionID],
| > | S.[Report_OID],
| > | S.[Locale],
| > | S.[InactiveFlags],
| > | S.[DeliveryExtension],
| > | S.[ExtensionSettings],
| > | SUSER_SNAME(Modified.[Sid]),
| > | Modified.[UserName],
| > | S.[ModifiedDate],
| > | S.[Description],
| > | S.[LastStatus],
| > | S.[EventType],
| > | S.[MatchData],
| > | S.[Parameters],
| > | S.[DataSettings],
| > | A.[TotalNotifications],
| > | A.[TotalSuccesses],
| > | A.[TotalFailures],
| > | SUSER_SNAME(Owner.[Sid]),
| > | Owner.[UserName],
| > | CAT.[Path],
| > | S.[LastRunTime],
| > | CAT.[Type],
| > | SD.NtSecDescPrimary
| > | from
| > | [Subscriptions] S inner join [Catalog] CAT on
| > S.[Report_OID]
| > =| > | CAT.[ItemID]
| > | inner join [Users] Owner on S.OwnerID = Owner.UserID
| > | inner join [Users] Modified on S.ModifiedByID =| > Modified.UserID
| > | left outer join [SecData] SD on CAT.[PolicyID] =| > SD.[PolicyID]
| > | AND SD.AuthType = @.AuthType
| > | left outer join [ActiveSubscriptions] A with (NOLOCK) on
| > | S.[SubscriptionID] = A.[SubscriptionID]";
| > |
| > | InstrumentedSqlCommand command1 = Storage.NewSqlCommand(text1,
| > | CommandType.Text, base.Connection, base.Transaction,
| > | base.SqlCommandTimeout);
| > | command1.Parameters.Add("@.AuthType",
| > | WebConfigUtil.AuthenticationType);
| > | if ((user != null) && (user != ""))
| > | {
| > | this.AddClause(ref text1, ref flag1, "Owner.[Sid] =| > @.UserSid");
| > | command1.Parameters.Add("@.UserSid",
Native.NameToSid(user));
| > | }
| > |
| > | <removed code>
| > | }
| > |
| > | Now the interesting bit is:
| > |
| > | this.AddClause(ref text1, ref flag1, "Owner.[Sid] =| > @.UserSid");
| > | command1.Parameters.Add("@.UserSid",
Native.NameToSid(user));
| > |
| > |
| > | Why are they trying to lookup a SID for my custom username? Of course
| > this
| > | fails, which results in a WHERE clause that fails to grabs our
| > | subscriptions.
| > |
| > | Has anyone else found this? Am I completely wrong?
| > |
| > | BTW: I'm using Reporting Services SP 2.
| > |
| > | James Snape (for Ryan Stevens)
| > |
| > |
| > |
| >
|
|
||||It's OK Peter, this issue is not troubling us because the workaround we are
using is to list all subscriptions and filter in our app server. Not great
but it works. I just thought you would like to know of the bug for inclusion
in your next SP.
Regards,
James Snape
"Peter Yang [MSFT]" <petery@.online.microsoft.com> wrote in message
news:r7qTJWizFHA.3472@.TK2MSFTNGXA02.phx.gbl...
> Hello James,
> If ANSI_NULLS is off, it shall return all users with NULL SID. I have
> reported this issue to the proper channel but there is no feedback yet. If
> we have any update on this, we will let you know.
> Also, since the issue relates to source code evaluation, I recommend that
> you open a Support incident with Microsoft Product Support Services so
> that
> a dedicated Support Professional can assist with this case. If you need
> any
> help in this regard, please let me know.
> For a complete list of Microsoft Product Support Services phone numbers,
> please go to the following address on the World Wide Web:
> http://support.microsoft.com/directory/overview.asp
> Best Regards,
> Peter Yang
> MCSE2000/2003, MCSA, MCDBA
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> =====================================================>
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>
> --
> | From: "James Snape" <jim_snape.at.hotmail.com@.online.nospam>
> | References: <OH8poixyFHA.2540@.TK2MSFTNGP09.phx.gbl>
> <te#MlbyyFHA.768@.TK2MSFTNGXA01.phx.gbl>
> | Subject: Re: BUG: Reporting Services Custom Security and Subscriptions?
> | Date: Fri, 7 Oct 2005 12:23:31 +0100
> | Lines: 140
> | X-Priority: 3
> | X-MSMail-Priority: Normal
> | X-Newsreader: Microsoft Outlook Express 6.00.2900.2670
> | X-RFC2646: Format=Flowed; Original
> | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2670
> | Message-ID: <uBkwSGzyFHA.2644@.TK2MSFTNGP09.phx.gbl>
> | Newsgroups: microsoft.public.sqlserver.reportingsvcs
> | NNTP-Posting-Host: ip-213-92-131-1.aramiska-arc.aramiska.net
> 213.92.131.1
> | Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP09.phx.gbl
> | Xref: TK2MSFTNGXA01.phx.gbl
> microsoft.public.sqlserver.reportingsvcs:53926
> | X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
> |
> | Hi Peter,
> |
> | A comparison against NULL is always false so your query below never
> returns
> | any results.
> |
> | Regards,
> | James Snape
> |
> | "Peter Yang [MSFT]" <petery@.online.microsoft.com> wrote in message
> | news:te%23MlbyyFHA.768@.TK2MSFTNGXA01.phx.gbl...
> | > Hello James,
> | >
> | > Based on my scope, if a report server user is not mapped to a Windows
> user
> | > sid, it shall be NULL. I think this shall work properly in SQL query.
> | >
> | > select * from users where sid=NULL
> | >
> | > Regards,
> | >
> | > Peter Yang
> | > MCSE2000/2003, MCSA, MCDBA
> | > Microsoft Online Partner Support
> | >
> | > When responding to posts, please "Reply to Group" via your newsreader
> so
> | > that others may learn and benefit from your issue.
> | >
> | > =====================================================> | >
> | >
> | >
> | > This posting is provided "AS IS" with no warranties, and confers no
> | > rights.
> | >
> | > --
> | > | From: "James Snape" <jim_snape.at.hotmail.com@.online.nospam>
> | > | Subject: BUG: Reporting Services Custom Security and Subscriptions?
> | > | Date: Fri, 7 Oct 2005 09:25:13 +0100
> | > | Lines: 75
> | > | X-Priority: 3
> | > | X-MSMail-Priority: Normal
> | > | X-Newsreader: Microsoft Outlook Express 6.00.2900.2670
> | > | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2670
> | > | X-RFC2646: Format=Flowed; Original
> | > | Message-ID: <OH8poixyFHA.2540@.TK2MSFTNGP09.phx.gbl>
> | > | Newsgroups: microsoft.public.sqlserver.reportingsvcs
> | > | NNTP-Posting-Host: ip-213-92-131-1.aramiska-arc.aramiska.net
> | > 213.92.131.1
> | > | Path:
> TK2MSFTNGXA01.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP09.phx.gbl
> | > | Xref: TK2MSFTNGXA01.phx.gbl
> | > microsoft.public.sqlserver.reportingsvcs:53918
> | > | X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
> | > |
> | > | Basically we have implemented custom security so that our logins to
> the
> | > | Report Server are using details in a custom database. They are not
> | > Windows
> | > | usernames/passwords. However when we use the web service api
> | > | (ListSubscriptions) to return user's subscriptions we get nothing?
> After
> | > | digging further with Reflector I found the following code in an RS
> | > assembly:
> | > |
> | > | public ArrayList ListSubscriptions(string user, string report)
> | > | {
> | > | ArrayList list1 = new ArrayList();
> | > | string text1 = "select
> | > | S.[SubscriptionID],
> | > | S.[Report_OID],
> | > | S.[Locale],
> | > | S.[InactiveFlags],
> | > | S.[DeliveryExtension],
> | > | S.[ExtensionSettings],
> | > | SUSER_SNAME(Modified.[Sid]),
> | > | Modified.[UserName],
> | > | S.[ModifiedDate],
> | > | S.[Description],
> | > | S.[LastStatus],
> | > | S.[EventType],
> | > | S.[MatchData],
> | > | S.[Parameters],
> | > | S.[DataSettings],
> | > | A.[TotalNotifications],
> | > | A.[TotalSuccesses],
> | > | A.[TotalFailures],
> | > | SUSER_SNAME(Owner.[Sid]),
> | > | Owner.[UserName],
> | > | CAT.[Path],
> | > | S.[LastRunTime],
> | > | CAT.[Type],
> | > | SD.NtSecDescPrimary
> | > | from
> | > | [Subscriptions] S inner join [Catalog] CAT on
> | > S.[Report_OID]
> | > => | > | CAT.[ItemID]
> | > | inner join [Users] Owner on S.OwnerID = Owner.UserID
> | > | inner join [Users] Modified on S.ModifiedByID => | > Modified.UserID
> | > | left outer join [SecData] SD on CAT.[PolicyID] => | > SD.[PolicyID]
> | > | AND SD.AuthType = @.AuthType
> | > | left outer join [ActiveSubscriptions] A with (NOLOCK)
> on
> | > | S.[SubscriptionID] = A.[SubscriptionID]";
> | > |
> | > | InstrumentedSqlCommand command1 = Storage.NewSqlCommand(text1,
> | > | CommandType.Text, base.Connection, base.Transaction,
> | > | base.SqlCommandTimeout);
> | > | command1.Parameters.Add("@.AuthType",
> | > | WebConfigUtil.AuthenticationType);
> | > | if ((user != null) && (user != ""))
> | > | {
> | > | this.AddClause(ref text1, ref flag1, "Owner.[Sid] => | > @.UserSid");
> | > | command1.Parameters.Add("@.UserSid",
> Native.NameToSid(user));
> | > | }
> | > |
> | > | <removed code>
> | > | }
> | > |
> | > | Now the interesting bit is:
> | > |
> | > | this.AddClause(ref text1, ref flag1, "Owner.[Sid] => | > @.UserSid");
> | > | command1.Parameters.Add("@.UserSid",
> Native.NameToSid(user));
> | > |
> | > |
> | > | Why are they trying to lookup a SID for my custom username? Of
> course
> | > this
> | > | fails, which results in a WHERE clause that fails to grabs our
> | > | subscriptions.
> | > |
> | > | Has anyone else found this? Am I completely wrong?
> | > |
> | > | BTW: I'm using Reporting Services SP 2.
> | > |
> | > | James Snape (for Ryan Stevens)
> | > |
> | > |
> | > |
> | >
> |
> |
> |
>|||Hello James,
Please rest assured that your feedback on this issue is routed to the
proper channel. Again, thank you for taking time to report this.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
| From: "James Snape" <jim_snape.at.hotmail.com@.online.nospam>
| References: <OH8poixyFHA.2540@.TK2MSFTNGP09.phx.gbl>
<te#MlbyyFHA.768@.TK2MSFTNGXA01.phx.gbl>
<uBkwSGzyFHA.2644@.TK2MSFTNGP09.phx.gbl>
<r7qTJWizFHA.3472@.TK2MSFTNGXA02.phx.gbl>
| Subject: Re: BUG: Reporting Services Custom Security and Subscriptions?
| Date: Tue, 11 Oct 2005 15:54:54 +0100
| Lines: 219
| X-Priority: 3
| X-MSMail-Priority: Normal
| X-Newsreader: Microsoft Outlook Express 6.00.2900.2670
| X-RFC2646: Format=Flowed; Original
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2670
| Message-ID: <upkv$OnzFHA.1264@.tk2msftngp13.phx.gbl>
| Newsgroups: microsoft.public.sqlserver.reportingsvcs
| NNTP-Posting-Host: exony-ltd-02.altohiway.com 213.83.66.226
| Path:
TK2MSFTNGXA02.phx.gbl!TK2MSFTNGXA01.phx.gbl!TK2MSFTNGP08.phx.gbl!tk2msftngp1
3.phx.gbl
| Xref: TK2MSFTNGXA02.phx.gbl microsoft.public.sqlserver.reportingsvcs:61002
| X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
|
| It's OK Peter, this issue is not troubling us because the workaround we
are
| using is to list all subscriptions and filter in our app server. Not
great
| but it works. I just thought you would like to know of the bug for
inclusion
| in your next SP.
|
| Regards,
| James Snape
|
| "Peter Yang [MSFT]" <petery@.online.microsoft.com> wrote in message
| news:r7qTJWizFHA.3472@.TK2MSFTNGXA02.phx.gbl...
| > Hello James,
| >
| > If ANSI_NULLS is off, it shall return all users with NULL SID. I have
| > reported this issue to the proper channel but there is no feedback yet.
If
| > we have any update on this, we will let you know.
| >
| > Also, since the issue relates to source code evaluation, I recommend
that
| > you open a Support incident with Microsoft Product Support Services so
| > that
| > a dedicated Support Professional can assist with this case. If you need
| > any
| > help in this regard, please let me know.
| >
| > For a complete list of Microsoft Product Support Services phone numbers,
| > please go to the following address on the World Wide Web:
| > http://support.microsoft.com/directory/overview.asp
| >
| > Best Regards,
| >
| > Peter Yang
| > MCSE2000/2003, MCSA, MCDBA
| > Microsoft Online Partner Support
| >
| > When responding to posts, please "Reply to Group" via your newsreader so
| > that others may learn and benefit from your issue.
| >
| > =====================================================| >
| >
| >
| > This posting is provided "AS IS" with no warranties, and confers no
| > rights.
| >
| >
| > --
| > | From: "James Snape" <jim_snape.at.hotmail.com@.online.nospam>
| > | References: <OH8poixyFHA.2540@.TK2MSFTNGP09.phx.gbl>
| > <te#MlbyyFHA.768@.TK2MSFTNGXA01.phx.gbl>
| > | Subject: Re: BUG: Reporting Services Custom Security and
Subscriptions?
| > | Date: Fri, 7 Oct 2005 12:23:31 +0100
| > | Lines: 140
| > | X-Priority: 3
| > | X-MSMail-Priority: Normal
| > | X-Newsreader: Microsoft Outlook Express 6.00.2900.2670
| > | X-RFC2646: Format=Flowed; Original
| > | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2670
| > | Message-ID: <uBkwSGzyFHA.2644@.TK2MSFTNGP09.phx.gbl>
| > | Newsgroups: microsoft.public.sqlserver.reportingsvcs
| > | NNTP-Posting-Host: ip-213-92-131-1.aramiska-arc.aramiska.net
| > 213.92.131.1
| > | Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP09.phx.gbl
| > | Xref: TK2MSFTNGXA01.phx.gbl
| > microsoft.public.sqlserver.reportingsvcs:53926
| > | X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
| > |
| > | Hi Peter,
| > |
| > | A comparison against NULL is always false so your query below never
| > returns
| > | any results.
| > |
| > | Regards,
| > | James Snape
| > |
| > | "Peter Yang [MSFT]" <petery@.online.microsoft.com> wrote in message
| > | news:te%23MlbyyFHA.768@.TK2MSFTNGXA01.phx.gbl...
| > | > Hello James,
| > | >
| > | > Based on my scope, if a report server user is not mapped to a
Windows
| > user
| > | > sid, it shall be NULL. I think this shall work properly in SQL
query.
| > | >
| > | > select * from users where sid=NULL
| > | >
| > | > Regards,
| > | >
| > | > Peter Yang
| > | > MCSE2000/2003, MCSA, MCDBA
| > | > Microsoft Online Partner Support
| > | >
| > | > When responding to posts, please "Reply to Group" via your
newsreader
| > so
| > | > that others may learn and benefit from your issue.
| > | >
| > | > =====================================================| > | >
| > | >
| > | >
| > | > This posting is provided "AS IS" with no warranties, and confers no
| > | > rights.
| > | >
| > | > --
| > | > | From: "James Snape" <jim_snape.at.hotmail.com@.online.nospam>
| > | > | Subject: BUG: Reporting Services Custom Security and
Subscriptions?
| > | > | Date: Fri, 7 Oct 2005 09:25:13 +0100
| > | > | Lines: 75
| > | > | X-Priority: 3
| > | > | X-MSMail-Priority: Normal
| > | > | X-Newsreader: Microsoft Outlook Express 6.00.2900.2670
| > | > | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2670
| > | > | X-RFC2646: Format=Flowed; Original
| > | > | Message-ID: <OH8poixyFHA.2540@.TK2MSFTNGP09.phx.gbl>
| > | > | Newsgroups: microsoft.public.sqlserver.reportingsvcs
| > | > | NNTP-Posting-Host: ip-213-92-131-1.aramiska-arc.aramiska.net
| > | > 213.92.131.1
| > | > | Path:
| > TK2MSFTNGXA01.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP09.phx.gbl
| > | > | Xref: TK2MSFTNGXA01.phx.gbl
| > | > microsoft.public.sqlserver.reportingsvcs:53918
| > | > | X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
| > | > |
| > | > | Basically we have implemented custom security so that our logins
to
| > the
| > | > | Report Server are using details in a custom database. They are not
| > | > Windows
| > | > | usernames/passwords. However when we use the web service api
| > | > | (ListSubscriptions) to return user's subscriptions we get nothing?
| > After
| > | > | digging further with Reflector I found the following code in an RS
| > | > assembly:
| > | > |
| > | > | public ArrayList ListSubscriptions(string user, string report)
| > | > | {
| > | > | ArrayList list1 = new ArrayList();
| > | > | string text1 = "select
| > | > | S.[SubscriptionID],
| > | > | S.[Report_OID],
| > | > | S.[Locale],
| > | > | S.[InactiveFlags],
| > | > | S.[DeliveryExtension],
| > | > | S.[ExtensionSettings],
| > | > | SUSER_SNAME(Modified.[Sid]),
| > | > | Modified.[UserName],
| > | > | S.[ModifiedDate],
| > | > | S.[Description],
| > | > | S.[LastStatus],
| > | > | S.[EventType],
| > | > | S.[MatchData],
| > | > | S.[Parameters],
| > | > | S.[DataSettings],
| > | > | A.[TotalNotifications],
| > | > | A.[TotalSuccesses],
| > | > | A.[TotalFailures],
| > | > | SUSER_SNAME(Owner.[Sid]),
| > | > | Owner.[UserName],
| > | > | CAT.[Path],
| > | > | S.[LastRunTime],
| > | > | CAT.[Type],
| > | > | SD.NtSecDescPrimary
| > | > | from
| > | > | [Subscriptions] S inner join [Catalog] CAT on
| > | > S.[Report_OID]
| > | > =| > | > | CAT.[ItemID]
| > | > | inner join [Users] Owner on S.OwnerID = Owner.UserID
| > | > | inner join [Users] Modified on S.ModifiedByID =| > | > Modified.UserID
| > | > | left outer join [SecData] SD on CAT.[PolicyID] =| > | > SD.[PolicyID]
| > | > | AND SD.AuthType = @.AuthType
| > | > | left outer join [ActiveSubscriptions] A with
(NOLOCK)
| > on
| > | > | S.[SubscriptionID] = A.[SubscriptionID]";
| > | > |
| > | > | InstrumentedSqlCommand command1 =Storage.NewSqlCommand(text1,
| > | > | CommandType.Text, base.Connection, base.Transaction,
| > | > | base.SqlCommandTimeout);
| > | > | command1.Parameters.Add("@.AuthType",
| > | > | WebConfigUtil.AuthenticationType);
| > | > | if ((user != null) && (user != ""))
| > | > | {
| > | > | this.AddClause(ref text1, ref flag1, "Owner.[Sid] =| > | > @.UserSid");
| > | > | command1.Parameters.Add("@.UserSid",
| > Native.NameToSid(user));
| > | > | }
| > | > |
| > | > | <removed code>
| > | > | }
| > | > |
| > | > | Now the interesting bit is:
| > | > |
| > | > | this.AddClause(ref text1, ref flag1, "Owner.[Sid] =| > | > @.UserSid");
| > | > | command1.Parameters.Add("@.UserSid",
| > Native.NameToSid(user));
| > | > |
| > | > |
| > | > | Why are they trying to lookup a SID for my custom username? Of
| > course
| > | > this
| > | > | fails, which results in a WHERE clause that fails to grabs our
| > | > | subscriptions.
| > | > |
| > | > | Has anyone else found this? Am I completely wrong?
| > | > |
| > | > | BTW: I'm using Reporting Services SP 2.
| > | > |
| > | > | James Snape (for Ryan Stevens)
| > | > |
| > | > |
| > | > |
| > | >
| > |
| > |
| > |
| >
|
|
|

BS7799 - all vote in Favour.

G/day forum,
Can anyone give me an insight into the effectiveness of using BS7799 as a
basis for a corporate security policy? I work in an E-Commorce environment
and now it transpires that a strictly enforced corporate security policy has
to be put in place.
Regards,
Steve.> Can anyone give me an insight into the effectiveness of using BS7799
> as a basis for a corporate security policy? I work in an E-Commorce
> environment and now it transpires that a strictly enforced corporate
> security policy has to be put in place.
Plus:
It saves you the trouble of writing all that text yourself and it's a
reasonable "middle of the road" baseline. And you won't have to explain to
anyone where you got that baseline from.
Minus: It might be a lot heavier than you really need. So you must do a risk
analysis first and then decide which measures you want to enforce (must
have) and which are "nice to have".
Then the problem is that the global guidelines of BS7799 still must be
translated to the workfloor. For example BS7799 won't tell you how to harden
your IIS webserver.|||Thanks Peter.
Take it that I've got very sensitive data stored in my network and I want to
harden down each system to a very secure standard.
Would this be a correct baseline? Is there any useful URL you could point me
towards (besides the Google search)? Do I have to purchase it?
Regards,
Steve.
"Petr Kazil" <pklist01_removethisnojunkmail_@.xs4all.nl> wrote in message
news:40506abc$0$570$e4fe514c@.news.xs4all.nl...
> Plus:
> It saves you the trouble of writing all that text yourself and it's a
> reasonable "middle of the road" baseline. And you won't have to explain to
> anyone where you got that baseline from.
> Minus: It might be a lot heavier than you really need. So you must do a
risk
> analysis first and then decide which measures you want to enforce (must
> have) and which are "nice to have".
> Then the problem is that the global guidelines of BS7799 still must be
> translated to the workfloor. For example BS7799 won't tell you how to
harden
> your IIS webserver.
>

Breaking a SQL Cluster

Is there a way to break my Active/Active cluster back to have a single
stand-alone SQL server?
internal politics and a rogue admin who applies security patches willie
nillie to the cluster nodes is causing more downtime than the cluster is
worth!
Hopefully there is a way that this can be done fairly painlessly.
Thanks in advance,
Ron G
You can uninstall a virtual server by running the installation wizard again.
The books online have the exact steps listed.
Cheers,
Rod
MVP - Windows Server - Clustering
http://www.nw-america.com - Clustering
http://msmvps.com/clustering - Blog
"Ron Griffin" <rgriffin@.lattestonetech.com> wrote in message
news:8A9Zd.19191$YD4.2485@.newssvr12.news.prodigy.c om...
> Is there a way to break my Active/Active cluster back to have a single
> stand-alone SQL server?
> internal politics and a rogue admin who applies security patches willie
> nillie to the cluster nodes is causing more downtime than the cluster is
> worth!
> Hopefully there is a way that this can be done fairly painlessly.
> Thanks in advance,
> Ron G
>

BPA vs. Security Best Practices paper

I would like to refer to the following technical article

SQL Server 2005 Security Best Practices - Operational and Administrative Tasks

http://www.microsoft.com/technet/prodtechnol/sql/2005/sql2005secbestpract.mspx

Among best practices for SQL Server service accounts on page 8, it is recommended to 'use a separate account for each service'. I created separate account for each service as advised and assign account to relevant Windows group created for each SQL Server service during SQL setup.

Now when I run Best Practices Analyzer, its report seemed to contradict what the above article said. For example, BPA reports excerpts:

"We recommend that the service SQLBrowser on host MachineName be run under Network Service Account". I get similar recommendation for SQLSERVERAGENT account as well. Most importantly, it recommends that MSFTESQL be run under SQL Server Service Account.

Can anyone of you shed some light on it?

Thanks,

Asaf

BPA is actually accurate with respect to SQL Browser. The sole purpose of this service is to resolve named instances. So, the first thing that you do on a machine that does not have a named instance is to disable the service. If you have named instances, then simply setting it to Network Service is sufficient, because it does not need to access any resources external of the machine that it is running on.

I run SQL Server Agent as a named Windows account and could really care less what BPA has to say about it. The reason that I run it under a named Windows account is that every system I've worked on has jobs that need access to a variety of resources. Many of these drop backups on machines other than where the instance is running or grab and move files from other systems. You can't perform any of those operations running under Network Service.

As for MSFTESQL, if you are not using full text indexing, disable this service. If you are using full text indexing, I use a separate account for the service so that I can isolate it and separately manage it from everything else.

The "separate account for each service" is a really great concept, but you have to determine what is best for you. If you have a single SQL Server instance, having a separate service account isn't that big of a deal from a management perspective. What happens when you are suddenly managing 10 instances, which really isn't that hard. Are you really going to create 40 different accounts - one each for Browser, SQL Server, SQL Server Agent, and Full Text for each instance? (Even more if you are using SSIS, SSRS, SSAS, etc.) Are you even going to be able to remember the different passwords for each? This separate account thing can be taken to idiotic proportions. I worked with one large organization whose security group dictated that each service on each machine was required to have a different account, which also meant a different strong password that was a minimum of 12 characters with upper, lower, numbers, and special characters. One of the sysadmins carries a large notebook with him when he leaves the office and it sits on his desk the rest of the time that contains over 40,000 logins/passwords just for the systems he is responsible for. One of the senior DBAs walks around with almost 15,000 logins/passwords on a PDA with a paper backup in his desk for the database servers he is responsible for. When you have to start writing down the logins and passwords in order to accomplish "separate account for each service", you have obviously taken things too far and defeated the purpose of doing this in the first place.

|||

Hi Michael,

I appreciate your comments and sharing your experience how complicated it could get if we decided to create separate account for each SQL Server machine. I manage 10 servers, all in DMZ, and I have been trying to decide on security policies.

Thanks Again,

Asaf

|||

For each server positioned in a 'DMZ', I recommend keeping individual service login accounts.

If any one server is ever compromised, you do not want to make it so easy for the others the be compromised.

Inside the DMZ, (double firewall), I agree with Michael's comments in respect to using a single highly guarded domain account (with exceptionally strong password) for SQL Servers, and I probably would not allow that domain account to have 'interactive login' priviledges..

|||

The interactive login privileges is a REALLY important one. Prior to SQL Server 2005, you didn't have a choice, since the service accounts used were local admins on the machines. But, service accounts for 2005 should NEVER have admin level authority, nor should they be able to login to the console. This brings up a really good example at another customer who has decided that under no circumstances will DBAs have access to the machines running their SQL Servers. There is an entirely different set of logins which are granted access to machines and only created for system admins. So, each system admin has two accounts, the one they login to their machine with and the one they login to servers with. What they forgot is that the DBAs are the ones who setup the SQL Servers and so they are given the service account and password. Since this is a SQL Server 2000 environment, they make those service accounts administrators on the machines. Then, since they are not allowed any access whatsoever to the machines their SQL Servers are running on, they bypass everything by using the service account to login to RDP so that they can actually do the things necessary to manage their SQL Servers. So, now it is impossible to actually audit any actions which occur. There are many examples of sheer stupidity in security policies.

Security systems exist for 2 reasons:

1. Keep the people out who shouldn't be there in the first place

2. Providing audit trails for those people authorized to perform actions

If you ever have a security policy that either impedes or prevents someone from taking legitimate actions which are required for their job, you need to throw the policy away and start over. Because, leaving such a policy in place will either damage your business, force the people who have to manage the systems to decide they should go somewhere that they are allowed to do their job, or will have the admins themselves concoting ways to bypass security leading to gaping holes that can also be exploited by hackers.

|||

Arnie,

Thanks for making an interesting point for having a separate account for each DMZ server. So after all extra bit of work required to set up individual accounts for each machine seem to provide an extra layer of protection.

Michael,

I do very much appreciate another valuable post from you. It reflects your deep understanding of security and years of experience in the industry.

Asaf