Problem with SQL services

When I reset or restart windows, the services mssqlserver
and the agent dont start.
I have this services configured with a administrator
domain user in the log on properties and for some reason
this services dont authenticate the password user I
received a error with the login but If I will to the
properties services and write the same password, the
service start.
The question is why dont start this services
automatically?
I change the password of this user and I have the same
problem.
My s.o. is win2000 sp4 and sql 2000 with sp3 in english
and this machine is a member of a w2000 domain.
I wait taht us understand me.
Regards,
Roque.In the properties of SQL Server (can be accessed from Enterprise manager by
right clicking on server), make sure "Auto start SQL Server Agent" is
checked.
ashish
"Roque Catanese" <anonymous@.discussions.microsoft.com> wrote in message
news:900e01c3e9cb$ab25f410$a601280a@.phx.gbl...
> When I reset or restart windows, the services mssqlserver
> and the agent dont start.
> I have this services configured with a administrator
> domain user in the log on properties and for some reason
> this services dont authenticate the password user I
> received a error with the login but If I will to the
> properties services and write the same password, the
> service start.
> The question is why dont start this services
> automatically?
> I change the password of this user and I have the same
> problem.
> My s.o. is win2000 sp4 and sql 2000 with sp3 in english
> and this machine is a member of a w2000 domain.
> I wait taht us understand me.
> Regards,
> Roque.

Problem with sp_tables_rowset;2 (Domain Users vs Domain Groups)

Recently we bought a 3rd party application that connects to our database
(using SQL Server OLEDB Provider and Integrated Security). One of the step
it does is to bring the list of table that user has access to.
This app works fine if we use a domain user and grant it access to the
database "individually". But it fails to retrieve list of tables if we use
the "Domain group" security model. i.e. a domain user does not exists in
db - instead the user is member of a domain group and this group is added to
database.
t
Using profiler, I tracked that the app uses an sp (sp_tables_rowset;2) to
retrieve list of tables. In "both" cases, the app was able to connect to
database without any problem. But this sp returns zero rows in case of
domain group. My aim is to minimize access rights management on the database
and do most of it on Active Directory - which is why I want to use domain
groups instead of individual domain users.
By reviewing the code below of the sp, a limit to the rows is done thru'
this clause: u.uid = user_id(). But a call to this method in query analyzer
"select user_id()", returns 0.
Here is the line that app uses to call the sp:
exec [mytestdb]..sp_tables_rowset;2 N'MyDomain\MyUser', NULL
Here is the code for the sp (i found on google):
create procedure sp_tables_rowset;2
(
@.table_schema varchar(255) = null,
@.table_type varchar(255) = null
)
as
select TABLE_CATALOG = db_name(),
TABLE_SCHEMA = user_name(o.uid),
TABLE_NAME = o.name,
TABLE_TYPE = convert(varchar(30),
case o.type
when 'U' then 'TABLE'
when 'V' then 'VIEW'
when 'S' then 'SYSTEM TABLE'
end),
TABLE_GUID = convert(binary(16), null),
DESCRIPTION = convert(varchar(1), null)
from sysusers u, sysobjects o
where o.type in ('U','V','S')
and ( @.table_schema is null
or @.table_schema = user_name(o.uid)
)
and (
@.table_type is null
or @.table_type = case o.type
when 'U' then 'TABLE'
when 'V' then 'VIEW'
when 'S' then 'SYSTEM TABLE'
end
)
and u.uid = user_id() /* constrain sysusers uid for use in subquery */
and (
suser_id() = 1 /* User is the System Administrator */
or o.uid = user_id() /* User created the object */
/* here's the magic... select the highest precedence of permissions in the
order (user,group,public) */
or ( (select max(((sign(uid)*abs(uid-16383))*2)+(protecttype&1))
from sysprotects p
/* join to correlate with all rows in sysobjects */
where p.id = o.id
/* get rows for public,current user,user's group */
and (p.uid = 0 or p.uid = user_id() or p.uid = u.gid)
/* check for SELECT,EXECUTE privilege */
and (action in (193,224)))&1 /* more magic...normalize GRANT */
) = 1 /* final magic...compare Grants */
)
order by 4, 2, 3Hi Salman,
First of all, You could see the codes of sp_tables_rowset;2 by doing
1. Open Query Analyzer as sa
2. use master
3. sp_helptext 'sp_tables_rowset'
you could see the definition of sp_tables_rowset;2, which have little
difference with the codes you given. Would you please tell me where do you
get that code?
Secondly, sp_tables_rowset is used to show table information, while the
first parameter is used to identify TABLE_SCHEMA, and the second one is
used to identify TABLE_TYPE. By running command exec sp_tables_rowset;2 ,
you could see all the tables in current database. Would you please confirm
that your Domain Users and Domain Groups both exist in TABLE_SCHEMA?
Thirdly, I did the following testing based on my understanding,
0. I tried it in Windows XP / 2003 English Version and SQL Server 2000 (SP3)
create a "testdb" database
1. Open Comuter Management, add a Group named "testGP" and an user named
"testUs" in Local Users and Groups separatly
2. Open SQL Server Entriprise Manager
adding 'testUs' and 'testGP' to Security -> Logins, give them
different psw
3. using testdb as default database and click testdb in Database Access
page.
4. confirm that "testUs" and "testGP" could be found at Database -> testdb
-> Users
5. Using "testUs" and "testGP" to login in with Query Analyzer
6. create a table separatly by using "testUs" and "testGP"
7. running exec sp_tables_rowset;2
running exec sp_tables_rowset;2, 'testUs'
running exec sp_tables_rowset;2, 'testGP'
and I will get the answer as expected, no matter what I logined as
8. running select user_id(), I could get different number, in my test, it's
5 and 6.
Bottom line - have a good backup strategy before any modifications.
Thank you for your patience and cooperation. If you have any questions or
concerns, don't hesitate to let me know. We are here to be of assistance!
Sincerely yours,
Michael Cheng
Microsoft Online Support
****************************************
*******************
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks.|||Salman,
It seems that sp_tables_rowset as it is being called is attempting to list
tables that are "owned" by the user, or (in new terms) where the schema name
is the same as the user_name.
Are there actually supposed to be different versions of a table? Such as:
mytestdb.Salman.FirstTable
mytestdb.Joseph.FirstTable
mytestdb.Richie.FirstTable
That seems unlikely to me, but certainly not impossible. If that is the
case, then you will need to make domain logins be users in the database.
You should not need to grant any rights to the users, their rights will come
through the domain groups, but the actual row in sysusers is necessary for
this all to work.
Are all objects in the database owned by dbo? If so, then the call to
sp_tables_rowset should be:
exec [mytestdb]..sp_tables_rowset;2 N'dbo', NULL
The application is obviously making some assumptions about how you connect
to the database, and what rights the user has in the database, that are
different from what you are expecting. (Yes, we have had some interesting
times with commercial software as well, but not this particular problem.)
If your problems persist, you will need to discuss this issue in detail with
your vendor to determine what assumptions they have made.
Russell Fields
"Salman" <salman_z_g@.hotmail.com> wrote in message
news:ekgaFSnJEHA.2776@.TK2MSFTNGP12.phx.gbl...
> Recently we bought a 3rd party application that connects to our database
> (using SQL Server OLEDB Provider and Integrated Security). One of the step
> it does is to bring the list of table that user has access to.
> This app works fine if we use a domain user and grant it access to the
> database "individually". But it fails to retrieve list of tables if we use
> the "Domain group" security model. i.e. a domain user does not exists in
> db - instead the user is member of a domain group and this group is added
to
> database.
> t
> Using profiler, I tracked that the app uses an sp (sp_tables_rowset;2) to
> retrieve list of tables. In "both" cases, the app was able to connect to
> database without any problem. But this sp returns zero rows in case of
> domain group. My aim is to minimize access rights management on the
database
> and do most of it on Active Directory - which is why I want to use domain
> groups instead of individual domain users.
> By reviewing the code below of the sp, a limit to the rows is done thru'
> this clause: u.uid = user_id(). But a call to this method in query
analyzer
> "select user_id()", returns 0.
> Here is the line that app uses to call the sp:
> exec [mytestdb]..sp_tables_rowset;2 N'MyDomain\MyUser', NULL
> Here is the code for the sp (i found on google):
> create procedure sp_tables_rowset;2
> (
> @.table_schema varchar(255) = null,
> @.table_type varchar(255) = null
> )
> as
> select TABLE_CATALOG = db_name(),
> TABLE_SCHEMA = user_name(o.uid),
> TABLE_NAME = o.name,
> TABLE_TYPE = convert(varchar(30),
> case o.type
> when 'U' then 'TABLE'
> when 'V' then 'VIEW'
> when 'S' then 'SYSTEM TABLE'
> end),
> TABLE_GUID = convert(binary(16), null),
> DESCRIPTION = convert(varchar(1), null)
> from sysusers u, sysobjects o
> where o.type in ('U','V','S')
> and ( @.table_schema is null
> or @.table_schema = user_name(o.uid)
> )
> and (
> @.table_type is null
> or @.table_type = case o.type
> when 'U' then 'TABLE'
> when 'V' then 'VIEW'
> when 'S' then 'SYSTEM TABLE'
> end
> )
> and u.uid = user_id() /* constrain sysusers uid for use in subquery */
> and (
> suser_id() = 1 /* User is the System Administrator */
> or o.uid = user_id() /* User created the object */
> /* here's the magic... select the highest precedence of permissions in
the
> order (user,group,public) */
> or ( (select max(((sign(uid)*abs(uid-16383))*2)+(protecttype&1))
> from sysprotects p
> /* join to correlate with all rows in sysobjects */
> where p.id = o.id
> /* get rows for public,current user,user's group */
> and (p.uid = 0 or p.uid = user_id() or p.uid = u.gid)
> /* check for SELECT,EXECUTE privilege */
> and (action in (193,224)))&1 /* more magic...normalize GRANT */
> ) = 1 /* final magic...compare Grants */
> )
> order by 4, 2, 3
>|||Hi Salman,
Moreover, please confirm that you have make the latest upgrades, which
could be download at
Microsoft SQL Server 2000 Service Pack 3a
http://www.microsoft.com/downloads/...d52c-0488-4e46-
afbf-acace5369fa3&DisplayLang=en
If I have some misunderstandings, please show me your steps as detailed as
possible that I could reporduce your situation on my machine.
Thank you for your patience and cooperation. If you have any questions or
concerns, don't hesitate to let me know. We are here to be of assistance!
Sincerely yours,
Michael Cheng
Microsoft Online Support
****************************************
*******************
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks.|||Thanks for your reply.
Responses are inline.
""Michael, Cheng [MSFT]"" <v-mingqc@.online.microsoft.com> wrote in messa
ge
news:gPcXmYtJEHA.480@.cpmsftngxa10.phx.gbl...
> Hi Salman,
> First of all, You could see the codes of sp_tables_rowset;2 by doing
> 1. Open Query Analyzer as sa
> 2. use master
> 3. sp_helptext 'sp_tables_rowset'
> you could see the definition of sp_tables_rowset;2, which have little
> difference with the codes you given. Would you please tell me where do you
> get that code?
I think i found the sp at this locations:
http://www.cis.usouthal.edu/share/N...all/instcat.sql
You are right - ran sp_helptext on my db and got a different one.
Where can I find documentation (usage scenarios) for this & similar sp?
I couldn't find it on msdn. How do 3rd party app vendors utilize these
stored procs if they are not searchable on msdn?

> Secondly, sp_tables_rowset is used to show table information, while the
> first parameter is used to identify TABLE_SCHEMA, and the second one is
> used to identify TABLE_TYPE. By running command exec sp_tables_rowset;2 ,
> you could see all the tables in current database. Would you please confirm
> that your Domain Users and Domain Groups both exist in TABLE_SCHEMA?
Is this compulsary to have domain users there? If yes - that means every
time I want a user added to have access, I will have to add it to the
database as well as the Active Directory group - which kind of defeats the
purpose of the group. Plz correct me if I m wrong. I might be missing some
basics ... please forward me some links to read about this?
Anyway, I tried it both ways. I have two domain users dom\usr1 and dom\usr2
in a domain group dom\grp1.
In my database -> Users, I can see dom\usr1 and dom\grp1 - but not dom\usr2.
Tried running sp_tables_rowset;2 '<username>' through both users and got no
results (because of the reason below).
I researched a little more on the problem. The 3rd party app is using
sp_oledb_ro_usrname first to find the user name & state (i think) of
database. And then it exec the sp_tables_rowset;2 to get list of tables and
passes the user name of prev sp as the table schema parameter value. In our
case, our target database is under table schema of dbo. So any call with
those user names specified returns no rows.
Waiting for ur reply.
Thanks again.

> Thirdly, I did the following testing based on my understanding,
> 0. I tried it in Windows XP / 2003 English Version and SQL Server 2000
(SP3)
> create a "testdb" database
> 1. Open Comuter Management, add a Group named "testGP" and an user named
> "testUs" in Local Users and Groups separatly
> 2. Open SQL Server Entriprise Manager
> adding 'testUs' and 'testGP' to Security -> Logins, give them
> different psw
> 3. using testdb as default database and click testdb in Database Access
> page.
> 4. confirm that "testUs" and "testGP" could be found at Database -> testdb
> -> Users
> 5. Using "testUs" and "testGP" to login in with Query Analyzer
> 6. create a table separatly by using "testUs" and "testGP"
> 7. running exec sp_tables_rowset;2
> running exec sp_tables_rowset;2, 'testUs'
> running exec sp_tables_rowset;2, 'testGP'
> and I will get the answer as expected, no matter what I logined as
> 8. running select user_id(), I could get different number, in my test,
it's
> 5 and 6.
> Bottom line - have a good backup strategy before any modifications.
> Thank you for your patience and cooperation. If you have any questions or
> concerns, don't hesitate to let me know. We are here to be of assistance!
> Sincerely yours,
> Michael Cheng
> Microsoft Online Support
> ****************************************
*******************
> Get Secure! - www.microsoft.com/security
> This posting is provided "as is" with no warranties and confers no rights.
> Please reply to newsgroups only, many thanks.
>|||Hi Salman,
Based on my understanding, when you add a domain group to Login, it is
TREATED as an "individual" user, which is to say, you could not login as
domain group\domain users when you just add domain groups as Login. You
could just login is using domain group name and its password instead of its
users.
If you want to all users to be under the same group login to SQL Server,
you'd better make a login for this group. All users under this group will
use the group name to login. That's why you could not find result by
running sp_tables_rowset;2 'dom\usr2' as you just add dom\grp1 into your
Login.
Moreover, we cannot provide documents for sp_tables_rowset as it is not
recommend. In the meanwhile, I recommend you have a look at
Administering SQL Server - Managing Security
http://msdn.microsoft.com/library/d...-us/adminsql/ad
_security_8pkj.asp
which describes the security tools built into Microsoft SQL Server 2000 and
includes information about : Security Architecture and Managing Security
Accounts
Hope this helps. Please also feel free to post in the group if you would
like further help. We are here to be of assistance!
Sincerely yours,
Michael Cheng
Microsoft Online Support
****************************************
*******************
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks.|||Hi Salman,
I wanted to post a quick note to see if you would like additional
assistance or information regarding this particular issue. We appreciate
your patience and look forward to hearing from you!
Sincerely yours,
Michael Cheng
Microsoft Online Support
****************************************
*******************
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks.

Problem with Service Principle Name after new install

Hi all,
I installed a SQL Server 2000 on a Win2003 machine, running the SQL Server
service under a domain user account.
When the SQL Server starts, I find a warning in the event log:
Source: MSSQLServer EventID: 19011
Description: SuperSocket info: (SpnRegister) : Error 8344.
After reading several KB articles I understand that the service account
tries to register the Service Principle Name on startup and fails because it
does not have the rights the register the SPN in Active Directory.
I put the account in the local admin group on the server and created a SPN
with setspn.exe from Win2000 resource kit, which points to at the domain
account. Does not fix the warning message.
I do not want to put the account into domain admin group, which would fix
the problem I think, because domain admins have all permissions to register
the SPN.
Does someone have a hint for me?
Thanks in advance
Regards
Christian
If your server should always work using same tcp port, you may ignore this
message.
If server works under LocalSystem or other account which has such
permissions (by default - only Domain Admins), he registers SPN at startup
and unregisters at shutdown. So in your case if you change tcp/ip settings
for server, you will have to reregister SPN manually. Or you may give such
rights to service account - see this article for permissions info:
http://technet2.microsoft.com/Window...e47441033.mspx
WBR, Evergray
Words mean nothing...
"Christian Guntsche" <christian.guntsche@.docuware.com> wrote in message
news:OxLkQH1RGHA.1688@.TK2MSFTNGP11.phx.gbl...
> Hi all,
> I installed a SQL Server 2000 on a Win2003 machine, running the SQL Server
> service under a domain user account.
> When the SQL Server starts, I find a warning in the event log:
> Source: MSSQLServer EventID: 19011
> Description: SuperSocket info: (SpnRegister) : Error 8344.
> After reading several KB articles I understand that the service account
> tries to register the Service Principle Name on startup and fails because
> it does not have the rights the register the SPN in Active Directory.
> I put the account in the local admin group on the server and created a SPN
> with setspn.exe from Win2000 resource kit, which points to at the domain
> account. Does not fix the warning message.
> I do not want to put the account into domain admin group, which would fix
> the problem I think, because domain admins have all permissions to
> register the SPN.
> Does someone have a hint for me?
> Thanks in advance
> Regards
> Christian
>
|||Thanks for reply,
at least you pointed us in the right direction.
I read the article, but again MS describes not which rights an account needs
in order to register the SPN at startup. All information you get is, that it
works with domain admin rights and with local system.
Nonetheless, the information, that a special function is executed in Active
Directory let us search through the Advanced Active Directory permission
settings and we found that giving the permission to "write public
information" to the SQL Service Account solves the problem.
This prevents us from assigning domain admin rights.
Strange, that Microsoft does not point out which specific permissions have
to be set for a normal user account, but in the other tells you to not use a
admin account.
Anyway this is solved for us.
Regards
Christian
"Oleksandr Chuchko" <forlists@.mail.ru> schrieb im Newsbeitrag
news:%23JIEvO5RGHA.5036@.TK2MSFTNGP12.phx.gbl...
> If your server should always work using same tcp port, you may ignore this
> message.
> If server works under LocalSystem or other account which has such
> permissions (by default - only Domain Admins), he registers SPN at startup
> and unregisters at shutdown. So in your case if you change tcp/ip settings
> for server, you will have to reregister SPN manually. Or you may give such
> rights to service account - see this article for permissions info:
> http://technet2.microsoft.com/Window...e47441033.mspx
> --
> WBR, Evergray
> --
> Words mean nothing...
>
> "Christian Guntsche" <christian.guntsche@.docuware.com> wrote in message
> news:OxLkQH1RGHA.1688@.TK2MSFTNGP11.phx.gbl...
>

Problem with Service Principle Name after new install

Hi all,
I installed a SQL Server 2000 on a Win2003 machine, running the SQL Server
service under a domain user account.
When the SQL Server starts, I find a warning in the event log:
Source: MSSQLServer EventID: 19011
Description: SuperSocket info: (SpnRegister) : Error 8344.
After reading several KB articles I understand that the service account
tries to register the Service Principle Name on startup and fails because it
does not have the rights the register the SPN in Active Directory.
I put the account in the local admin group on the server and created a SPN
with setspn.exe from Win2000 resource kit, which points to at the domain
account. Does not fix the warning message.
I do not want to put the account into domain admin group, which would fix
the problem I think, because domain admins have all permissions to register
the SPN.
Does someone have a hint for me?
Thanks in advance
Regards
ChristianIf your server should always work using same tcp port, you may ignore this
message.
If server works under LocalSystem or other account which has such
permissions (by default - only Domain Admins), he registers SPN at startup
and unregisters at shutdown. So in your case if you change tcp/ip settings
for server, you will have to reregister SPN manually. Or you may give such
rights to service account - see this article for permissions info:
http://technet2.microsoft.com/Windo...de47441033.mspx
WBR, Evergray
--
Words mean nothing...
"Christian Guntsche" <christian.guntsche@.docuware.com> wrote in message
news:OxLkQH1RGHA.1688@.TK2MSFTNGP11.phx.gbl...
> Hi all,
> I installed a SQL Server 2000 on a Win2003 machine, running the SQL Server
> service under a domain user account.
> When the SQL Server starts, I find a warning in the event log:
> Source: MSSQLServer EventID: 19011
> Description: SuperSocket info: (SpnRegister) : Error 8344.
> After reading several KB articles I understand that the service account
> tries to register the Service Principle Name on startup and fails because
> it does not have the rights the register the SPN in Active Directory.
> I put the account in the local admin group on the server and created a SPN
> with setspn.exe from Win2000 resource kit, which points to at the domain
> account. Does not fix the warning message.
> I do not want to put the account into domain admin group, which would fix
> the problem I think, because domain admins have all permissions to
> register the SPN.
> Does someone have a hint for me?
> Thanks in advance
> Regards
> Christian
>|||Thanks for reply,
at least you pointed us in the right direction.
I read the article, but again MS describes not which rights an account needs
in order to register the SPN at startup. All information you get is, that it
works with domain admin rights and with local system.
Nonetheless, the information, that a special function is executed in Active
Directory let us search through the Advanced Active Directory permission
settings and we found that giving the permission to "write public
information" to the SQL Service Account solves the problem.
This prevents us from assigning domain admin rights.
Strange, that Microsoft does not point out which specific permissions have
to be set for a normal user account, but in the other tells you to not use a
admin account.
Anyway this is solved for us.
Regards
Christian
"Oleksandr Chuchko" <forlists@.mail.ru> schrieb im Newsbeitrag
news:%23JIEvO5RGHA.5036@.TK2MSFTNGP12.phx.gbl...
> If your server should always work using same tcp port, you may ignore this
> message.
> If server works under LocalSystem or other account which has such
> permissions (by default - only Domain Admins), he registers SPN at startup
> and unregisters at shutdown. So in your case if you change tcp/ip settings
> for server, you will have to reregister SPN manually. Or you may give such
> rights to service account - see this article for permissions info:
> http://technet2.microsoft.com/Windo...de47441033.mspx
> --
> WBR, Evergray
> --
> Words mean nothing...
>
> "Christian Guntsche" <christian.guntsche@.docuware.com> wrote in message
> news:OxLkQH1RGHA.1688@.TK2MSFTNGP11.phx.gbl...
>sql

Problem with Service Principle Name after new install

Hi all,
I installed a SQL Server 2000 on a Win2003 machine, running the SQL Server
service under a domain user account.
When the SQL Server starts, I find a warning in the event log:
Source: MSSQLServer EventID: 19011
Description: SuperSocket info: (SpnRegister) : Error 8344.
After reading several KB articles I understand that the service account
tries to register the Service Principle Name on startup and fails because it
does not have the rights the register the SPN in Active Directory.
I put the account in the local admin group on the server and created a SPN
with setspn.exe from Win2000 resource kit, which points to at the domain
account. Does not fix the warning message.
I do not want to put the account into domain admin group, which would fix
the problem I think, because domain admins have all permissions to register
the SPN.
Does someone have a hint for me?
Thanks in advance
Regards
ChristianIf your server should always work using same tcp port, you may ignore this
message.
If server works under LocalSystem or other account which has such
permissions (by default - only Domain Admins), he registers SPN at startup
and unregisters at shutdown. So in your case if you change tcp/ip settings
for server, you will have to reregister SPN manually. Or you may give such
rights to service account - see this article for permissions info:
http://technet2.microsoft.com/WindowsServer/en/Library/8127f5ed-4e05-4822-bfa9-402ceede47441033.mspx
--
WBR, Evergray
--
Words mean nothing...
"Christian Guntsche" <christian.guntsche@.docuware.com> wrote in message
news:OxLkQH1RGHA.1688@.TK2MSFTNGP11.phx.gbl...
> Hi all,
> I installed a SQL Server 2000 on a Win2003 machine, running the SQL Server
> service under a domain user account.
> When the SQL Server starts, I find a warning in the event log:
> Source: MSSQLServer EventID: 19011
> Description: SuperSocket info: (SpnRegister) : Error 8344.
> After reading several KB articles I understand that the service account
> tries to register the Service Principle Name on startup and fails because
> it does not have the rights the register the SPN in Active Directory.
> I put the account in the local admin group on the server and created a SPN
> with setspn.exe from Win2000 resource kit, which points to at the domain
> account. Does not fix the warning message.
> I do not want to put the account into domain admin group, which would fix
> the problem I think, because domain admins have all permissions to
> register the SPN.
> Does someone have a hint for me?
> Thanks in advance
> Regards
> Christian
>|||Thanks for reply,
at least you pointed us in the right direction. :)
I read the article, but again MS describes not which rights an account needs
in order to register the SPN at startup. All information you get is, that it
works with domain admin rights and with local system.
Nonetheless, the information, that a special function is executed in Active
Directory let us search through the Advanced Active Directory permission
settings and we found that giving the permission to "write public
information" to the SQL Service Account solves the problem.
This prevents us from assigning domain admin rights.
Strange, that Microsoft does not point out which specific permissions have
to be set for a normal user account, but in the other tells you to not use a
admin account.
Anyway this is solved for us.
Regards
Christian
"Oleksandr Chuchko" <forlists@.mail.ru> schrieb im Newsbeitrag
news:%23JIEvO5RGHA.5036@.TK2MSFTNGP12.phx.gbl...
> If your server should always work using same tcp port, you may ignore this
> message.
> If server works under LocalSystem or other account which has such
> permissions (by default - only Domain Admins), he registers SPN at startup
> and unregisters at shutdown. So in your case if you change tcp/ip settings
> for server, you will have to reregister SPN manually. Or you may give such
> rights to service account - see this article for permissions info:
> http://technet2.microsoft.com/WindowsServer/en/Library/8127f5ed-4e05-4822-bfa9-402ceede47441033.mspx
> --
> WBR, Evergray
> --
> Words mean nothing...
>
> "Christian Guntsche" <christian.guntsche@.docuware.com> wrote in message
> news:OxLkQH1RGHA.1688@.TK2MSFTNGP11.phx.gbl...
>> Hi all,
>> I installed a SQL Server 2000 on a Win2003 machine, running the SQL
>> Server service under a domain user account.
>> When the SQL Server starts, I find a warning in the event log:
>> Source: MSSQLServer EventID: 19011
>> Description: SuperSocket info: (SpnRegister) : Error 8344.
>> After reading several KB articles I understand that the service account
>> tries to register the Service Principle Name on startup and fails because
>> it does not have the rights the register the SPN in Active Directory.
>> I put the account in the local admin group on the server and created a
>> SPN with setspn.exe from Win2000 resource kit, which points to at the
>> domain account. Does not fix the warning message.
>> I do not want to put the account into domain admin group, which would fix
>> the problem I think, because domain admins have all permissions to
>> register the SPN.
>> Does someone have a hint for me?
>> Thanks in advance
>> Regards
>> Christian
>

Problem with reference dimension & snowflaked tables

OK, this is a bit complicated, so I hope I can explain it clearly. I'll try not to get bogged down in the details of the problem domain, but bear with me.

We have a cube with a regular dimension and a reference dimension. The reference dimension is connected through the fact table through the regular dimension (there are many other dimensions as well, but I'll simplify the situation here). Both the regular dimension and the reference dimension have hierarchies that that include as their top level a level called "System". For both of the dimensions, the "System" level is snowflaked in from the same table. So the regular dimension table contains a column called System_key, and a corresponding attribute. Similarly, the reference dimension table also contains a column called System_key, and a corresponding attribute. Both keys point in to the same table. Naturally, the regular dimension table also has a column that points to the reference dimension table, as you would expect with a reference dimension.

OK, I hope that's clear (if a little pathological). Essentially, the "System" attribute is present on both dimensions, and both dimensions get that value by snowflaking in to the same table.

So the problem comes when looking at measures broken down by System. The regular dimension has a "System" user hierarchy which contains a single level, the System level. When I drag that hierarchy on to a pivot chart, I get the wrong results.

From what I can tell, when processing the dimension it is populating the system attribute for the regular dimension based on the key value stored in the reference dimension table, not the regular dimension table. I have no idea why it would do that. But I look at the SQL statement generated during processing of the key attribute for the regular dimension, and it includes in the where statement a join between the reference dimension System_key column and the System table, rather than the regular dimension System_key column as I would expect. The result is totally wrong values.

Does this make any kind of sense? Any idea what could be causing this, and what might fix it (note that totally redoing all the incestuous table relationships at this point is probably not an option).What if, in the Data Source View, you create another instance of the "System" attribute table, using a simple Named Query like: "select * from System"? Then you snowflake one instance with the regular dimension table, and the other instance with the reference dimension table. My guess (maybe someone can confirm this?) is that, unless you explicitly use role-playing dimensions (like Date in Adventure Works), a single snowflaked table in the DSV is interpreted as a single instance, even if it's incorporated into the schema of multiple dimensions. And in your case, I'm assuming that breaking the "System" attribute out into its own role-playing dimension is not an option?|||Deekpak,
Thanks for the suggestion. That does seem to solve the problem.

However, it does seem like a bit of a hack. It would be nice to know if this is a bug in SSAS that may be fixed in the future, or if there's something fundamental that I don't understand here. We snowflake in this table in multiple other dimensions as well (though none others with reference dimensions currently), so it would be good to know if we're setting ourselves for more trouble later.

Any 'Softies out there care to comment?