Thursday, March 8, 2012
Bug in MSKB918992 ("How to transfer the logins...") - How to report?
I found a bug in the listing in Knowledge Base Article 918992 ("How to
transfer the logins and the passwords between instances of SQL Server
2005"). Instead of
SET @.tmpstr = 'CREATE LOGIN ' + QUOTENAME( @.name ) + ' WITH PASSWORD = ' +
@.PWD_string + ' HASHED, SID = ' + @.SID_string + ' DEFAULT_DATABASE = ['
+
@.defaultdb + ']'
it should be
SET @.tmpstr = 'CREATE LOGIN ' + QUOTENAME( @.name ) + ' WITH PASSWORD = ' +
@.PWD_string + ' HASHED, SID = ' + @.SID_string + ', DEFAULT_DATABASE = ['
+
@.defaultdb + ']'
A colon has to be added before DEFAULT_DATABASE. Now while this little bit
of information might be useful to somebody here, the primary reason for this
posting is that the stupid interface for reporting bugs on the MS web site
(http://connect.microsoft.com/feedba...aspx?SiteID=68) drove me
nuts! It wouldn't let me sign in for reporting bugs, I could only search. Is
this offering restricted to a selection of VIP users or something? Can
anybody enlighten me here? I was just trying to be helpful...
Thanks and best regards
Nils LoeberBy VIPs perhaps you meant MVPs, and yes, they can report bugs via that site.
However, you should be able to also, and so should I. I got stuck in the
loop where you keep getting back to the page telling you that you need to
sign in to submit feedback. I've pinged a member of the dev team to find out
if they really meant for everybody to send email to the administrator
account listed on https://connect.microsoft.com/SQLServer. At the moment,
that would appear to be the only option to get access but I don't think it's
meant to be gated like that.
In the meantime, however, at the bottom of every KB article on the support
site is a section that allows you to submit comments/bugs. Scroll to the
bottom of http://support.microsoft.com/kb/918992/en-us and have a look. The
support folks will be reading that information and can correct the article
directly.
Sincerely,
Stephen Dybing
This posting is provided "AS IS" with no warranties, and confers no rights.
"Nils Loeber" <nils@.NOSPAMFORMEPLEASEnils-loeber.de> wrote in message
news:u1eXq%23xiGHA.5036@.TK2MSFTNGP04.phx.gbl...
> Hi all,
> I found a bug in the listing in Knowledge Base Article 918992 ("How to
> transfer the logins and the passwords between instances of SQL Server
> 2005"). Instead of
> SET @.tmpstr = 'CREATE LOGIN ' + QUOTENAME( @.name ) + ' WITH PASSWORD = ' +
> @.PWD_string + ' HASHED, SID = ' + @.SID_string + ' DEFAULT_DATABASE = [
' +
> @.defaultdb + ']'
> it should be
> SET @.tmpstr = 'CREATE LOGIN ' + QUOTENAME( @.name ) + ' WITH PASSWORD = ' +
> @.PWD_string + ' HASHED, SID = ' + @.SID_string + ', DEFAULT_DATABASE = [
;' +
> @.defaultdb + ']'
> A colon has to be added before DEFAULT_DATABASE. Now while this little bit
> of information might be useful to somebody here, the primary reason for
> this posting is that the stupid interface for reporting bugs on the MS web
> site (http://connect.microsoft.com/feedba...aspx?SiteID=68) drove
> me nuts! It wouldn't let me sign in for reporting bugs, I could only
> search. Is this offering restricted to a selection of VIP users or
> something? Can anybody enlighten me here? I was just trying to be
> helpful...
> Thanks and best regards
> Nils Loeber
>|||OK, the text has been cleaned up a little bit in the attempt to make it
clear that you have to search for the issue before you can submit new
feedback. This is done in the attempt to cut down on duplicates. Try the
search to find out if it's already been reported and if not, if you have the
ability to submit a new issue. I'll point out that it's not allowing me to
yet, they're working on it...
Sincerely,
Stephen Dybing
This posting is provided "AS IS" with no warranties, and confers no rights.
"Steve Dybing [MSFT]" <steve.dybing@.online.microsoft.com> wrote in messa
ge
news:uR%23M$SkjGHA.4884@.TK2MSFTNGP03.phx.gbl...
> By VIPs perhaps you meant MVPs, and yes, they can report bugs via that
> site. However, you should be able to also, and so should I. I got stuck in
> the loop where you keep getting back to the page telling you that you need
> to sign in to submit feedback. I've pinged a member of the dev team to
> find out if they really meant for everybody to send email to the
> administrator account listed on https://connect.microsoft.com/SQLServer.
> At the moment, that would appear to be the only option to get access but I
> don't think it's meant to be gated like that.
> In the meantime, however, at the bottom of every KB article on the support
> site is a section that allows you to submit comments/bugs. Scroll to the
> bottom of http://support.microsoft.com/kb/918992/en-us and have a look.
> The support folks will be reading that information and can correct the
> article directly.
> --
> Sincerely,
> Stephen Dybing
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> "Nils Loeber" <nils@.NOSPAMFORMEPLEASEnils-loeber.de> wrote in message
> news:u1eXq%23xiGHA.5036@.TK2MSFTNGP04.phx.gbl...
>|||By VIPs perhaps you meant MVPs, and yes, they can report bugs via that site.
However, you should be able to also, and so should I. I got stuck in the
loop where you keep getting back to the page telling you that you need to
sign in to submit feedback. I've pinged a member of the dev team to find out
if they really meant for everybody to send email to the administrator
account listed on https://connect.microsoft.com/SQLServer. At the moment,
that would appear to be the only option to get access but I don't think it's
meant to be gated like that.
In the meantime, however, at the bottom of every KB article on the support
site is a section that allows you to submit comments/bugs. Scroll to the
bottom of http://support.microsoft.com/kb/918992/en-us and have a look. The
support folks will be reading that information and can correct the article
directly.
Sincerely,
Stephen Dybing
This posting is provided "AS IS" with no warranties, and confers no rights.
"Nils Loeber" <nils@.NOSPAMFORMEPLEASEnils-loeber.de> wrote in message
news:u1eXq%23xiGHA.5036@.TK2MSFTNGP04.phx.gbl...
> Hi all,
> I found a bug in the listing in Knowledge Base Article 918992 ("How to
> transfer the logins and the passwords between instances of SQL Server
> 2005"). Instead of
> SET @.tmpstr = 'CREATE LOGIN ' + QUOTENAME( @.name ) + ' WITH PASSWORD = ' +
> @.PWD_string + ' HASHED, SID = ' + @.SID_string + ' DEFAULT_DATABASE = [
' +
> @.defaultdb + ']'
> it should be
> SET @.tmpstr = 'CREATE LOGIN ' + QUOTENAME( @.name ) + ' WITH PASSWORD = ' +
> @.PWD_string + ' HASHED, SID = ' + @.SID_string + ', DEFAULT_DATABASE = [
;' +
> @.defaultdb + ']'
> A colon has to be added before DEFAULT_DATABASE. Now while this little bit
> of information might be useful to somebody here, the primary reason for
> this posting is that the stupid interface for reporting bugs on the MS web
> site (http://connect.microsoft.com/feedba...aspx?SiteID=68) drove
> me nuts! It wouldn't let me sign in for reporting bugs, I could only
> search. Is this offering restricted to a selection of VIP users or
> something? Can anybody enlighten me here? I was just trying to be
> helpful...
> Thanks and best regards
> Nils Loeber
>|||OK, the text has been cleaned up a little bit in the attempt to make it
clear that you have to search for the issue before you can submit new
feedback. This is done in the attempt to cut down on duplicates. Try the
search to find out if it's already been reported and if not, if you have the
ability to submit a new issue. I'll point out that it's not allowing me to
yet, they're working on it...
Sincerely,
Stephen Dybing
This posting is provided "AS IS" with no warranties, and confers no rights.
"Steve Dybing [MSFT]" <steve.dybing@.online.microsoft.com> wrote in messa
ge
news:uR%23M$SkjGHA.4884@.TK2MSFTNGP03.phx.gbl...
> By VIPs perhaps you meant MVPs, and yes, they can report bugs via that
> site. However, you should be able to also, and so should I. I got stuck in
> the loop where you keep getting back to the page telling you that you need
> to sign in to submit feedback. I've pinged a member of the dev team to
> find out if they really meant for everybody to send email to the
> administrator account listed on https://connect.microsoft.com/SQLServer.
> At the moment, that would appear to be the only option to get access but I
> don't think it's meant to be gated like that.
> In the meantime, however, at the bottom of every KB article on the support
> site is a section that allows you to submit comments/bugs. Scroll to the
> bottom of http://support.microsoft.com/kb/918992/en-us and have a look.
> The support folks will be reading that information and can correct the
> article directly.
> --
> Sincerely,
> Stephen Dybing
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> "Nils Loeber" <nils@.NOSPAMFORMEPLEASEnils-loeber.de> wrote in message
> news:u1eXq%23xiGHA.5036@.TK2MSFTNGP04.phx.gbl...
>|||OK, we figured out what the problem was for me. It appears that either I
hadn't accepted the terms or fully registered. I went back and logged in,
then clicked Manage Your Connect Profile in the lower left corner, accepted
the terms and completed the registration, and now after I've searched on a
string, below the results box I see a Submit Feedback button. If what you
want to provide feedback on cannot be found, click that button and submit
it.
Hope that helps,
Stephen Dybing
This posting is provided "AS IS" with no warranties, and confers no rights.
"Steve Dybing [MSFT]" <steve.dybing@.online.microsoft.com> wrote in messa
ge
news:eXbH$fkjGHA.3848@.TK2MSFTNGP04.phx.gbl...
> OK, the text has been cleaned up a little bit in the attempt to make it
> clear that you have to search for the issue before you can submit new
> feedback. This is done in the attempt to cut down on duplicates. Try the
> search to find out if it's already been reported and if not, if you have
> the ability to submit a new issue. I'll point out that it's not allowing
> me to yet, they're working on it...
> --
> Sincerely,
> Stephen Dybing
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> "Steve Dybing [MSFT]" <steve.dybing@.online.microsoft.com> wrote in mes
sage
> news:uR%23M$SkjGHA.4884@.TK2MSFTNGP03.phx.gbl...
>|||OK, we figured out what the problem was for me. It appears that either I
hadn't accepted the terms or fully registered. I went back and logged in,
then clicked Manage Your Connect Profile in the lower left corner, accepted
the terms and completed the registration, and now after I've searched on a
string, below the results box I see a Submit Feedback button. If what you
want to provide feedback on cannot be found, click that button and submit
it.
Hope that helps,
Stephen Dybing
This posting is provided "AS IS" with no warranties, and confers no rights.
"Steve Dybing [MSFT]" <steve.dybing@.online.microsoft.com> wrote in messa
ge
news:eXbH$fkjGHA.3848@.TK2MSFTNGP04.phx.gbl...
> OK, the text has been cleaned up a little bit in the attempt to make it
> clear that you have to search for the issue before you can submit new
> feedback. This is done in the attempt to cut down on duplicates. Try the
> search to find out if it's already been reported and if not, if you have
> the ability to submit a new issue. I'll point out that it's not allowing
> me to yet, they're working on it...
> --
> Sincerely,
> Stephen Dybing
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> "Steve Dybing [MSFT]" <steve.dybing@.online.microsoft.com> wrote in mes
sage
> news:uR%23M$SkjGHA.4884@.TK2MSFTNGP03.phx.gbl...
>|||Hello Steve,
"Steve Dybing [MSFT]" <steve.dybing@.online.microsoft.com> schrieb im
Newsbeitrag news:uR%23M$SkjGHA.4884@.TK2MSFTNGP03.phx.gbl...
> In the meantime, however, at the bottom of every KB article on the support
> site is a section that allows you to submit comments/bugs. Scroll to the
> bottom of http://support.microsoft.com/kb/918992/en-us and have a look.
> The support folks will be reading that information and can correct the
> article directly.
Great, that seems to have worked. It's just that I couldn't see it the first
time around, because on my browser of choice (Firefox) the lowest quarter of
the page doesn't get displayed (same problem with all kb entries).
Thank you and best regards,
Nils Loeber|||Hello Steve,
"Steve Dybing [MSFT]" <steve.dybing@.online.microsoft.com> schrieb im
Newsbeitrag news:uR%23M$SkjGHA.4884@.TK2MSFTNGP03.phx.gbl...
> In the meantime, however, at the bottom of every KB article on the support
> site is a section that allows you to submit comments/bugs. Scroll to the
> bottom of http://support.microsoft.com/kb/918992/en-us and have a look.
> The support folks will be reading that information and can correct the
> article directly.
Great, that seems to have worked. It's just that I couldn't see it the first
time around, because on my browser of choice (Firefox) the lowest quarter of
the page doesn't get displayed (same problem with all kb entries).
Thank you and best regards,
Nils Loeber|||After you connect to the site with Firefox, try doing a
refresh. I hit the same issue with the page displays and
that generally works for me.
-Sue
On Tue, 13 Jun 2006 12:22:59 +0200, "Nils Loeber"
<nils@.NOSPAMFORMEPLEASEnils-loeber.de> wrote:
>because on my browser of choice (Firefox) the lowest quarter of
>the page doesn't get displayed (same problem with all kb entries).
Wednesday, March 7, 2012
bug described in article 872843
sql server is on SP4.
I try to tweak Log reader Agent Properties & other things but that is not
helping.
It is my impression whether this bug is not fixed or got break in SP4.
Any reply/feedback/solutions/tips will be highly appreciated.
Can you try to reapply the SP. There have been instances where the sp was
not completely applied.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Jay" <sqldba@.abc.com> wrote in message
news:7f4e272a5b544937af6958bf9119db34@.ureader.com. ..
>I am experiencing same problem described in article 872843. even though my
> sql server is on SP4.
> I try to tweak Log reader Agent Properties & other things but that is not
> helping.
> It is my impression whether this bug is not fixed or got break in SP4.
> Any reply/feedback/solutions/tips will be highly appreciated.
Sunday, February 19, 2012
Browse Cube Causes CPU Spike & Unresponsive SSAS
Hello,
I'm interested to see if anyone else has seen the behavior I have experienced. I could have missed a KB article, however, hoping that someone will help with some advice anyhow.
I have a cube (it is around 400 mb). Every morning I execute a SSIS package to process the cube (full process). This works every morning without any failures. When I came in this morning, I opened Management Studio, connected to SSAS, went to the cube, right clicked, and chose browse.
In Task Manager, the Mem Usage by msmdsrv.exe started around 411,000. Once I chose browse, the cpu jumped to 40-60 percent. It has been almost 30 minutes now, and the cpu is still in that range. The mem usage has risen to 530,000, but I expected this.
The problem is the CPU. I can't see any reason for such heavy cpu usage for 30 minutes +. If I restart the SSAS service, and browse the cube again, it seems to work fine. I have seen this happne 4-5 times now.
We are using SQL Server 2005 enterprise (no SP) on Windows Server 2003 SP 1. The machine has 1.5 gig of ram.
Any suggestions or direction is greatly appreciated.
Regards,
Dan
This is strange.
It is hard to say what is going on.
Try and run Profiler connect to Analysis Server and see what it is doing during these 30 min. See what kind of activity reported.
Try and run ActivityViewer sample application and see which sessions are active and what is getting executed.
Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
Thanks for your response Edward. I followed your suggestions, but I'm not sure it yielded any useful results. Below is what I have typed up & pasted as I was looking through things.
I’ll start from the ActivityViewer.While I got a few errors from the JIT compiler getting it to run, I managed to hit continue each time and saw some interesting information.While I’m not 100% on how to interpret the findings, this is what I found:
Under Sessions, I found 25295. It is on the same cube/database that I tried to “Browse”.It appears that all the times show something around 12pm (although it is 8am… I’m going to ignore this for the time being) and under last command it has MDSCHEMA_CUBES.
If find this under “Transactions”
TRANSACTION_ID TRANSACTION_SESSION_ID TRANSACTION_START_TIME TRANSACTION_ELAPSED_TIME_MS TRANSACTION_CPU_TIME_MS
96EC1996-B252-4F80-A0FA-330EE37476BB
25A40F0E-A6FD-4FBA-A6FD-17DFFC0F2340
7/6/2006 12:36 PM
4176799
31
And this under “Locks”.The same Transaction ID above is causing the Lock.
SPID LOCK_ID LOCK_TRANSACTION_ID LOCK_OBJECT_ID LOCK_STATUS LOCK_TYPE LOCK_CREATION_TIME LOCK_GRANT_TIME
25295
c6eceba6-19bc-480b-96fb-989273e4eade
96ec1996-b252-4f80-a0fa-330ee37476bb
<Object xmlns="urn:schemas-microsoft-com:xml-analysis:rowset"><DatabaseID>dw_prep_archive_PROD</DatabaseID></Object>
1
8
7/6/2006 12:36 PM
7/6/2006 12:36 PM
The time shown under the “lock creation time” is the time that I tried to browse the cube this morning.The profiler trace now shows 13:48 so this whole process has been occurring for over an hour (and my cpu is still above 45%).
This first set of data (below) from the profiler might show something useful, but I'm not sure what it means.
The second set below (seperated by a line) shows the set of commands that loop over and over again every 2 minutes or so. I know below is incredibly messy, but it basically shows “Flight recorder begin” followed by “discover locks”, “discover transactions”, “discover locks”, and then “Flight Recorder Snapshot End”.
Although I am relatively new to SSAS, I don’t think I see anything that could be causing this.Does anyone have any insight on what is happening here?
I really don't expect anyone to try to decipher below completely, but if anyone sees any unusual commands, I would appreciate it.
The only thing I can see is that my initial connection to browse the cube is trying to do something and in a transaction and is blocking everything else.
Regards,
Dan
Upon Connection
Discover Begin 26 - DISCOVER_PROPERTIES <RestrictionList xmlns="urn:schemas-microsoft-com:xml-analysis">
<PropertyName>ProviderVersion</PropertyName>
</RestrictionList> 101 DMLENZ Microsoft SQL Server Management Studio 2006-07-06 12:35:20.000 2006-07-06 12:35:20.000 3572 25267 QG <PropertyList xmlns="urn:schemas-microsoft-com:xml-analysis"> <Timeout>0</Timeout> <SspropInitAppName>Micros
Discover End 26 - DISCOVER_PROPERTIES <RestrictionList xmlns="urn:schemas-microsoft-com:xml-analysis">
<PropertyName>ProviderVersion</PropertyName>
</RestrictionList> 101 DMLENZ Microsoft SQL Server Management Studio 2006-07-06 12:35:20.000 2006-07-06 12:35:20.000 0 0 3572 25267 0 QG <PropertyList xmlns="urn:schemas-microsoft-com:xml-analysis"> <Timeout>0</Timeout> <SspropInitAppName>Micros
Discover Begin 31 - DISCOVER_XML_METADATA <RestrictionList xmlns="urn:schemas-microsoft-com:xml-analysis"><ObjectExpansion>ExpandFull</ObjectExpansion></RestrictionList> 99 DMLENZ Microsoft SQL Server Management Studio 2006-07-06 12:35:22.000 2006-07-06 12:35:22.000 25271 QG
Discover End 31 - DISCOVER_XML_METADATA <RestrictionList xmlns="urn:schemas-microsoft-com:xml-analysis"><ObjectExpansion>ExpandFull</ObjectExpansion></RestrictionList> 99 DMLENZ Microsoft SQL Server Management Studio 2006-07-06 12:35:22.000 2006-07-06 12:35:37.000 15107 0 25271 2625 QG
Discover Begin 26 - DISCOVER_PROPERTIES <RestrictionList xmlns="urn:schemas-microsoft-com:xml-analysis">
<PropertyName>DBMSVersion</PropertyName>
</RestrictionList> 101 DMLENZ Microsoft SQL Server Management Studio 2006-07-06 12:35:39.000 2006-07-06 12:35:39.000 3572 25267 QG <PropertyList xmlns="urn:schemas-microsoft-com:xml-analysis"> <Timeout>0</Timeout> <SspropInitAppName>Micros
Discover End 26 - DISCOVER_PROPERTIES <RestrictionList xmlns="urn:schemas-microsoft-com:xml-analysis">
<PropertyName>DBMSVersion</PropertyName>
</RestrictionList> 101 DMLENZ Microsoft SQL Server Management Studio 2006-07-06 12:35:39.000 2006-07-06 12:35:39.000 0 0 3572 25267 0 QG <PropertyList xmlns="urn:schemas-microsoft-com:xml-analysis"> <Timeout>0</Timeout> <SspropInitAppName>Micros
Discover Begin 31 - DISCOVER_XML_METADATA <RestrictionList xmlns="urn:schemas-microsoft-com:xml-analysis"><ObjectExpansion>ExpandObject</ObjectExpansion></RestrictionList> 103 DMLENZ Microsoft SQL Server Management Studio 2006-07-06 12:35:44.000 2006-07-06 12:35:44.000 25280 QG
Discover End 31 - DISCOVER_XML_METADATA <RestrictionList xmlns="urn:schemas-microsoft-com:xml-analysis"><ObjectExpansion>ExpandObject</ObjectExpansion></RestrictionList> 103 DMLENZ Microsoft SQL Server Management Studio 2006-07-06 12:35:44.000 2006-07-06 12:35:44.000 47 0 25280 47 QG
Discover Begin 31 - DISCOVER_XML_METADATA <RestrictionList xmlns="urn:schemas-microsoft-com:xml-analysis"><DatabaseID>dw_prep_archive_PROD</DatabaseID><ObjectExpansion>ExpandObject</ObjectExpansion></RestrictionList> 102 DMLENZ Microsoft SQL Server Management Studio 2006-07-06 12:35:48.000 2006-07-06 12:35:48.000 25284 QG
Discover End 31 - DISCOVER_XML_METADATA <RestrictionList xmlns="urn:schemas-microsoft-com:xml-analysis"><DatabaseID>dw_prep_archive_PROD</DatabaseID><ObjectExpansion>ExpandObject</ObjectExpansion></RestrictionList> 102 DMLENZ Microsoft SQL Server Management Studio 2006-07-06 12:35:48.000 2006-07-06 12:35:48.000 31 0 25284 31 QG
Discover Begin 31 - DISCOVER_XML_METADATA <RestrictionList xmlns="urn:schemas-microsoft-com:xml-analysis">
<ObjectExpansion>ExpandObject</ObjectExpansion>
</RestrictionList> 105 DMLENZ Microsoft SQL Server Management Studio 2006-07-06 12:35:50.000 2006-07-06 12:35:50.000 3572 25289 QG <PropertyList xmlns="urn:schemas-microsoft-com:xml-analysis"> <Timeout>0</Timeout> <SspropInitAppName>Micros
Discover End 31 - DISCOVER_XML_METADATA <RestrictionList xmlns="urn:schemas-microsoft-com:xml-analysis">
<ObjectExpansion>ExpandObject</ObjectExpansion>
</RestrictionList> 105 DMLENZ Microsoft SQL Server Management Studio 2006-07-06 12:35:50.000 2006-07-06 12:35:50.000 47 0 3572 25289 47 QG <PropertyList xmlns="urn:schemas-microsoft-com:xml-analysis"> <Timeout>0</Timeout> <SspropInitAppName>Micros
Discover Begin 31 - DISCOVER_XML_METADATA <RestrictionList xmlns="urn:schemas-microsoft-com:xml-analysis">
<DatabaseID>dw_prep_archive_PROD</DatabaseID>
<ObjectExpansion>ExpandObject</ObjectExpansion>
</RestrictionList> 105 DMLENZ Microsoft SQL Server Management Studio 2006-07-06 12:35:52.000 2006-07-06 12:35:52.000 3572 25289 QG <PropertyList xmlns="urn:schemas-microsoft-com:xml-analysis"> <Timeout>0</Timeout> <SspropInitAppName>Micros
Discover End 31 - DISCOVER_XML_METADATA <RestrictionList xmlns="urn:schemas-microsoft-com:xml-analysis">
<DatabaseID>dw_prep_archive_PROD</DatabaseID>
<ObjectExpansion>ExpandObject</ObjectExpansion>
</RestrictionList> 105 DMLENZ Microsoft SQL Server Management Studio 2006-07-06 12:35:52.000 2006-07-06 12:35:52.000 31 0 3572 25289 31 QG <PropertyList xmlns="urn:schemas-microsoft-com:xml-analysis"> <Timeout>0</Timeout> <SspropInitAppName>Micros
Discover Begin 26 - DISCOVER_PROPERTIES <RestrictionList xmlns="urn:schemas-microsoft-com:xml-analysis">
<PropertyName>DBMSVersion</PropertyName>
</RestrictionList> 106 DMLENZ Microsoft SQL Server Management Studio 2006-07-06 12:36:04.000 2006-07-06 12:36:04.000 3572 25295 QG <PropertyList xmlns="urn:schemas-microsoft-com:xml-analysis"> <Catalog>dw_prep_archive_PROD</Catalog> <ShowH
Discover End 26 - DISCOVER_PROPERTIES <RestrictionList xmlns="urn:schemas-microsoft-com:xml-analysis">
<PropertyName>DBMSVersion</PropertyName>
</RestrictionList> 106 DMLENZ Microsoft SQL Server Management Studio 2006-07-06 12:36:04.000 2006-07-06 12:36:04.000 0 0 3572 25295 0 QG <PropertyList xmlns="urn:schemas-microsoft-com:xml-analysis"> <Catalog>dw_prep_archive_PROD</Catalog> <ShowH
Discover Begin 4 - MDSCHEMA_CUBES <RestrictionList xmlns="urn:schemas-microsoft-com:xml-analysis">
<CATALOG_NAME>dw_prep_archive_PROD</CATALOG_NAME>
<CUBE_NAME>DW PREP ARCHIVE</CUBE_NAME>
<CUBE_SOURCE>3</CUBE_SOURCE>
</RestrictionList> 106 DMLENZ Microsoft SQL Server Management Studio 2006-07-06 12:36:04.000 2006-07-06 12:36:04.000 3572 25295 QG <PropertyList xmlns="urn:schemas-microsoft-com:xml-analysis"> <Catalog>dw_prep_archive_PROD</Catalog> <ShowH
Discover End 4 - MDSCHEMA_CUBES <RestrictionList xmlns="urn:schemas-microsoft-com:xml-analysis">
<CATALOG_NAME>dw_prep_archive_PROD</CATALOG_NAME>
<CUBE_NAME>DW PREP ARCHIVE</CUBE_NAME>
<CUBE_SOURCE>3</CUBE_SOURCE>
</RestrictionList> 106 DMLENZ Microsoft SQL Server Management Studio 2006-07-06 12:36:04.000 2006-07-06 12:36:04.000 78 0 3572 25295 16 QG <PropertyList xmlns="urn:schemas-microsoft-com:xml-analysis"> <Catalog>dw_prep_archive_PROD</Catalog> <ShowH
Discover Begin 4 - MDSCHEMA_CUBES 106 DMLENZ Microsoft SQL Server Management Studio 2006-07-06 12:36:09.000 2006-07-06 12:36:09.000 3572 25295 QG <PropertyList xmlns="urn:schemas-microsoft-com:xml-analysis"> <Catalog>dw_prep_archive_PROD</Catalog> <ShowH
Discover End 4 - MDSCHEMA_CUBES 106 DMLENZ Microsoft SQL Server Management Studio 2006-07-06 12:36:09.000 2006-07-06 12:36:09.000 0 0 3572 25295 0 QG <PropertyList xmlns="urn:schemas-microsoft-com:xml-analysis"> <Catalog>dw_prep_archive_PROD</Catalog> <ShowH
Discover Begin 4 - MDSCHEMA_CUBES <RestrictionList xmlns="urn:schemas-microsoft-com:xml-analysis">
<CUBE_SOURCE>3</CUBE_SOURCE>
</RestrictionList> 106 DMLENZ Microsoft SQL Server Management Studio 2006-07-06 12:36:09.000 2006-07-06 12:36:09.000 3572 25295 QG <PropertyList xmlns="urn:schemas-microsoft-com:xml-analysis"> <Catalog>dw_prep_archive_PROD</Catalog> <ShowH
Discover End 4 - MDSCHEMA_CUBES <RestrictionList xmlns="urn:schemas-microsoft-com:xml-analysis">
<CUBE_SOURCE>3</CUBE_SOURCE>
</RestrictionList> 106 DMLENZ Microsoft SQL Server Management Studio 2006-07-06 12:36:09.000 2006-07-06 12:36:09.000 16 0 3572 25295 16 QG <PropertyList xmlns="urn:schemas-microsoft-com:xml-analysis"> <Catalog>dw_prep_archive_PROD</Catalog> <ShowH
Discover Begin 5 - MDSCHEMA_DIMENSIONS <RestrictionList xmlns="urn:schemas-microsoft-com:xml-analysis">
<CUBE_NAME>DW PREP ARCHIVE</CUBE_NAME>
<CUBE_SOURCE>3</CUBE_SOURCE>
</RestrictionList> 106 DMLENZ Microsoft SQL Server Management Studio 2006-07-06 12:36:10.000 2006-07-06 12:36:10.000 3572 25295 QG <PropertyList xmlns="urn:schemas-microsoft-com:xml-analysis"> <Catalog>dw_prep_archive_PROD</Catalog> <ShowH
Query Subcube 2 - Non-cache data 00000000,000000000000000000000000,0000000,00,00000000,00000000,00000000 0 2006-07-06 12:36:10.000 2006-07-06 12:36:10.000 797 dw_prep_archive_PROD 25307 531
Query Subcube 2 - Non-cache data 00000000,000000000100100000011000,0000000,00,00000000,00000000,00000000 0 2006-07-06 12:36:10.000 2006-07-06 12:36:11.000 219 dw_prep_archive_PROD 25307 1156
Query Subcube 2 - Non-cache data 00000000,000000000100100000011000,0000000,00,00000000,00000000,00000000 0 2006-07-06 12:36:19.000 2006-07-06 12:36:19.000 406 dw_prep_archive_PROD 25307 9656
Profiler that "loops"
Notification11 - Flight Recorder Snapshot Begin0 2006-07-06 12:52:29.0002006-07-06 12:52:29.000 25544
Server State Discover Begin8 - DISCOVER_LOCKS<RestrictionList xmlns="urn:schemas-microsoft-com:xml-analysis">
<LOCK_MIN_TOTAL_MS>0</LOCK_MIN_TOTAL_MS>
</RestrictionList>0 2006-07-06 12:52:29.0002006-07-06 12:52:29.000 25545 <PropertyList xmlns="urn:schemas-microsoft-com:xml-analysis"></PropertyList>
Server State Discover End8 - DISCOVER_LOCKS<RestrictionList xmlns="urn:schemas-microsoft-com:xml-analysis">
<LOCK_MIN_TOTAL_MS>0</LOCK_MIN_TOTAL_MS>
</RestrictionList>0 2006-07-06 12:52:29.0002006-07-06 12:52:29.0000255450
Server State Discover Begin7 - DISCOVER_JOBS<RestrictionList xmlns="urn:schemas-microsoft-com:xml-analysis">
<JOB_MIN_TOTAL_TIME_MS>0</JOB_MIN_TOTAL_TIME_MS>
</RestrictionList>0 2006-07-06 12:52:29.0002006-07-06 12:52:29.000 25546 <PropertyList xmlns="urn:schemas-microsoft-com:xml-analysis"></PropertyList>
Server State Discover End7 - DISCOVER_JOBS<RestrictionList xmlns="urn:schemas-microsoft-com:xml-analysis">
<JOB_MIN_TOTAL_TIME_MS>0</JOB_MIN_TOTAL_TIME_MS>
</RestrictionList>0 2006-07-06 12:52:29.0002006-07-06 12:52:29.0000255460
Server State Discover Begin2 - DISCOVER_SESSIONS<RestrictionList xmlns="urn:schemas-microsoft-com:xml-analysis">
</RestrictionList>0 2006-07-06 12:52:29.0002006-07-06 12:52:29.000 25547 <PropertyList xmlns="urn:schemas-microsoft-com:xml-analysis"></PropertyList>
Server State Discover End2 - DISCOVER_SESSIONS<RestrictionList xmlns="urn:schemas-microsoft-com:xml-analysis">
</RestrictionList>0 2006-07-06 12:52:29.0002006-07-06 12:52:29.0000255470
Server State Discover Begin1 - DISCOVER_CONNECTIONS<RestrictionList xmlns="urn:schemas-microsoft-com:xml-analysis">
</RestrictionList>0 2006-07-06 12:52:29.0002006-07-06 12:52:29.000 25548 <PropertyList xmlns="urn:schemas-microsoft-com:xml-analysis"></PropertyList>
Server State Discover End1 - DISCOVER_CONNECTIONS<RestrictionList xmlns="urn:schemas-microsoft-com:xml-analysis">
</RestrictionList>0 2006-07-06 12:52:29.0002006-07-06 12:52:29.0000255480
Notification12 - Flight Recorder Snapshot End0 2006-07-06 12:52:29.0002006-07-06 12:52:29.000025544
|||This is great investigation you've done!
Unfortunately, I think the situation probably indicates problem with Analysis Services.
Dont see anything you should be doing differently.
Before you go and log your case with Customer Support Services can you please try and find a machine you can install Analysis Services SP1 into.
And see if your situaion is solved in SP1.
Feel free to contact me if you get stuck with your efforts.
Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
Thanks for your suggestions Edward.
I'm pretty sure I found the culprit. Whether it is a bug in SSAS (or bad mdx), the person who created the cube has calculations and named sets using the calculations. I'm not entirely sure what the purpose of them were, however when I removed the named sets. the problem was solved. The cube allows me to click "Browse" without any hangups.
I am in the process of utilizing a support contract we have to determine if this is a problem.
Thanks again.
Regards,
Dan
Sunday, February 12, 2012
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
Friday, February 10, 2012
Both Forms Authentication and Active Directory on one Server?
Authentication (for extranet users). According to the MSDN article on Forms
Authentication "It is not possible to run a report server under a mixed-mode
security system (for example, both Windows and Forms Authentication). "
Is it possible to create two Report and Report Manager applications on the
same server, with different virtual directory names, and set one to Anonymous
and the other to Integrated Authentication?
If yes, would there be a licensing issue?
RobSince the licensing is server-based, I don't think that would be an issue.
If it can work like you describe, I'd love to see it happen.
--
Cheers,
'(' Jeff A. Stucker
\
Business Intelligence
www.criadvantage.com
---
"Rob" <Rob@.discussions.microsoft.com> wrote in message
news:C3DC8047-12E7-4878-98FA-3AA2F92BF246@.microsoft.com...
>I need to provide both Active Directory (for intranet users) and Forms
> Authentication (for extranet users). According to the MSDN article on
> Forms
> Authentication "It is not possible to run a report server under a
> mixed-mode
> security system (for example, both Windows and Forms Authentication). "
> Is it possible to create two Report and Report Manager applications on the
> same server, with different virtual directory names, and set one to
> Anonymous
> and the other to Integrated Authentication?
> If yes, would there be a licensing issue?
> Rob