Gurus,
I noticed when my SQL Server Agent is stopped, what appears in SSMS is the
following:
"SQL Server Agent (Agent XPs disabled)"
Agent XPs are extended stored procedures.
http://msdn2.microsoft.com/en-us/library/ms178127.aspx
My question is do regular stored procedures still run?
Spin
Spin,
Yes, they do. Agent XPs disable really means just the SQL Agent procedures
that SQL Agent uses to get its job done.
RLF
"Spin" <Spin@.invalid.com> wrote in message
news:647jmvF2ae2t8U1@.mid.individual.net...
> Gurus,
> I noticed when my SQL Server Agent is stopped, what appears in SSMS is the
> following:
> "SQL Server Agent (Agent XPs disabled)"
> Agent XPs are extended stored procedures.
> http://msdn2.microsoft.com/en-us/library/ms178127.aspx
> My question is do regular stored procedures still run?
> --
> Spin
>
sql
Showing posts with label gurus. Show all posts
Showing posts with label gurus. Show all posts
Thursday, March 29, 2012
Do regular stored procedures still run when Agent XPs are disabled?
Gurus,
I noticed when my SQL Server Agent is stopped, what appears in SSMS is the
following:
"SQL Server Agent (Agent XPs disabled)"
Agent XPs are extended stored procedures.
http://msdn2.microsoft.com/en-us/library/ms178127.aspx
My question is do regular stored procedures still run?
--
SpinSpin,
Yes, they do. Agent XPs disable really means just the SQL Agent procedures
that SQL Agent uses to get its job done.
RLF
"Spin" <Spin@.invalid.com> wrote in message
news:647jmvF2ae2t8U1@.mid.individual.net...
> Gurus,
> I noticed when my SQL Server Agent is stopped, what appears in SSMS is the
> following:
> "SQL Server Agent (Agent XPs disabled)"
> Agent XPs are extended stored procedures.
> http://msdn2.microsoft.com/en-us/library/ms178127.aspx
> My question is do regular stored procedures still run?
> --
> Spin
>
I noticed when my SQL Server Agent is stopped, what appears in SSMS is the
following:
"SQL Server Agent (Agent XPs disabled)"
Agent XPs are extended stored procedures.
http://msdn2.microsoft.com/en-us/library/ms178127.aspx
My question is do regular stored procedures still run?
--
SpinSpin,
Yes, they do. Agent XPs disable really means just the SQL Agent procedures
that SQL Agent uses to get its job done.
RLF
"Spin" <Spin@.invalid.com> wrote in message
news:647jmvF2ae2t8U1@.mid.individual.net...
> Gurus,
> I noticed when my SQL Server Agent is stopped, what appears in SSMS is the
> following:
> "SQL Server Agent (Agent XPs disabled)"
> Agent XPs are extended stored procedures.
> http://msdn2.microsoft.com/en-us/library/ms178127.aspx
> My question is do regular stored procedures still run?
> --
> Spin
>
Tuesday, March 27, 2012
Do local administrators on a system with SQL Server 2005 installed have an implied 'sysad
Gurus,
Is it true that local administrators on a system with SQL Server 2005
installed have an implied 'sysadmin' right to SQL Server?
Spin
"Zarko Jovanovic" <mind_lessIsHsAsTsEsSsPsAsMs@.inet.hr> wrote in message
news:fr8dkh$olp$4@.sunce.iskon.hr...
> simple: YES !
How can I stop this behavior?
|||Unless the OS is Vista+SQL Server2005 SP2 (may be also WinServer2008+SQL
Server2005SP2).
"Zarko Jovanovic" <mind_lessIsHsAsTsEsSsPsAsMs@.inet.hr> wrote in message
news:fr8dkh$olp$4@.sunce.iskon.hr...
> Spin wrote:
> simple: YES !
|||On Mar 12, 7:29Xam, "Spin" <S...@.spin.com> wrote:
> "Zarko Jovanovic" <mind_lessIsHsAsTsEsSsPsA...@.inet.hr> wrote in message
> news:fr8dkh$olp$4@.sunce.iskon.hr...
>
> How can I stop this behavior?
It just so happens that I had to figure out how to disable this myself
just last week. I worked on SQL 2000, but hopefully this will work on
2005 as well.
I put this into a .sql file and called it using osql, in this way I
was able to scipt this change and execute it on our hundreds of
different servers very quickly.
However, you could just drop this into query analyzer (built into EM
in 2005 I recall) and run it:
exec sp_denylogin [BUILTIN\administrators]
That is it, that will deby local admin access to MSSQL. I also
scripted the changing of the sa password if you need that it is:
exec sp_Password 'OriginalPasswordHere', 'New_passwordHere1', 'sa'
Hope that helps!
|||On Mar 13, 9:13Xam, Tonagon <tony.robe...@.marriott.com> wrote:
> On Mar 12, 7:29Xam, "Spin" <S...@.spin.com> wrote:
>
>
> It just so happens that I had to figure out how to disable this myself
> just last week. XI worked on SQL 2000, but hopefully this will work on
> 2005 as well.
> I put this into a .sql file and called it using osql, in this way I
> was able to scipt this change and execute it on our hundreds of
> different servers very quickly.
> However, you could just drop this into query analyzer (built into EM
> in 2005 I recall) and run it:
> exec sp_denylogin [BUILTIN\administrators]
> That is it, that will deby local admin access to MSSQL. XI also
> scripted the changing of the sa password if you need that it is:
> exec sp_Password 'OriginalPasswordHere', 'New_passwordHere1', 'sa'
> Hope that helps!
OH! Something to look out for though. If you deny login access to the
Windows Admin group then the SQL Agent service will stop running and
say it cannot connect. By default it will be connecting using a local
Windows Admin account. That will have to be changed (you can right
click it in EM and go to properties and change it).
I found a script for that as well, but it is rather long and I don't
even understand the whole thing. It works though! If you need that as
well I can put it in here.
Is it true that local administrators on a system with SQL Server 2005
installed have an implied 'sysadmin' right to SQL Server?
Spin
"Zarko Jovanovic" <mind_lessIsHsAsTsEsSsPsAsMs@.inet.hr> wrote in message
news:fr8dkh$olp$4@.sunce.iskon.hr...
> simple: YES !
How can I stop this behavior?
|||Unless the OS is Vista+SQL Server2005 SP2 (may be also WinServer2008+SQL
Server2005SP2).
"Zarko Jovanovic" <mind_lessIsHsAsTsEsSsPsAsMs@.inet.hr> wrote in message
news:fr8dkh$olp$4@.sunce.iskon.hr...
> Spin wrote:
> simple: YES !
|||On Mar 12, 7:29Xam, "Spin" <S...@.spin.com> wrote:
> "Zarko Jovanovic" <mind_lessIsHsAsTsEsSsPsA...@.inet.hr> wrote in message
> news:fr8dkh$olp$4@.sunce.iskon.hr...
>
> How can I stop this behavior?
It just so happens that I had to figure out how to disable this myself
just last week. I worked on SQL 2000, but hopefully this will work on
2005 as well.
I put this into a .sql file and called it using osql, in this way I
was able to scipt this change and execute it on our hundreds of
different servers very quickly.
However, you could just drop this into query analyzer (built into EM
in 2005 I recall) and run it:
exec sp_denylogin [BUILTIN\administrators]
That is it, that will deby local admin access to MSSQL. I also
scripted the changing of the sa password if you need that it is:
exec sp_Password 'OriginalPasswordHere', 'New_passwordHere1', 'sa'
Hope that helps!
|||On Mar 13, 9:13Xam, Tonagon <tony.robe...@.marriott.com> wrote:
> On Mar 12, 7:29Xam, "Spin" <S...@.spin.com> wrote:
>
>
> It just so happens that I had to figure out how to disable this myself
> just last week. XI worked on SQL 2000, but hopefully this will work on
> 2005 as well.
> I put this into a .sql file and called it using osql, in this way I
> was able to scipt this change and execute it on our hundreds of
> different servers very quickly.
> However, you could just drop this into query analyzer (built into EM
> in 2005 I recall) and run it:
> exec sp_denylogin [BUILTIN\administrators]
> That is it, that will deby local admin access to MSSQL. XI also
> scripted the changing of the sa password if you need that it is:
> exec sp_Password 'OriginalPasswordHere', 'New_passwordHere1', 'sa'
> Hope that helps!
OH! Something to look out for though. If you deny login access to the
Windows Admin group then the SQL Agent service will stop running and
say it cannot connect. By default it will be connecting using a local
Windows Admin account. That will have to be changed (you can right
click it in EM and go to properties and change it).
I found a script for that as well, but it is rather long and I don't
even understand the whole thing. It works though! If you need that as
well I can put it in here.
Do local administrators on a system with SQL Server 2005 installed have an implied 'sysad
Gurus,
Is it true that local administrators on a system with SQL Server 2005
installed have an implied 'sysadmin' right to SQL Server?
--
SpinSpin wrote:
> Gurus,
> Is it true that local administrators on a system with SQL Server 2005
> installed have an implied 'sysadmin' right to SQL Server?
simple: YES !|||"Zarko Jovanovic" <mind_lessIsHsAsTsEsSsPsAsMs@.inet.hr> wrote in message
news:fr8dkh$olp$4@.sunce.iskon.hr...
> simple: YES !
How can I stop this behavior?|||Spin wrote:
> "Zarko Jovanovic" <mind_lessIsHsAsTsEsSsPsAsMs@.inet.hr> wrote in message
> news:fr8dkh$olp$4@.sunce.iskon.hr...
>> simple: YES !
> How can I stop this behavior?
>
1 first, be sure you give some windows account (yours maybe) sysadmin
rights on sql server!!!
2 then, check 1 again!!!
3 then, remove BUILTIN\administrators login from logins on SQL server|||Unless the OS is Vista+SQL Server2005 SP2 (may be also WinServer2008+SQL
Server2005SP2).
"Zarko Jovanovic" <mind_lessIsHsAsTsEsSsPsAsMs@.inet.hr> wrote in message
news:fr8dkh$olp$4@.sunce.iskon.hr...
> Spin wrote:
>> Gurus,
>> Is it true that local administrators on a system with SQL Server 2005
>> installed have an implied 'sysadmin' right to SQL Server?
> simple: YES !|||On Mar 12, 7:29=A0am, "Spin" <S...@.spin.com> wrote:
> "Zarko Jovanovic" <mind_lessIsHsAsTsEsSsPsA...@.inet.hr> wrote in message
> news:fr8dkh$olp$4@.sunce.iskon.hr...
> > simple: YES !
> How can I stop this behavior?
It just so happens that I had to figure out how to disable this myself
just last week. I worked on SQL 2000, but hopefully this will work on
2005 as well.
I put this into a .sql file and called it using osql, in this way I
was able to scipt this change and execute it on our hundreds of
different servers very quickly.
However, you could just drop this into query analyzer (built into EM
in 2005 I recall) and run it:
exec sp_denylogin [BUILTIN\administrators]
That is it, that will deby local admin access to MSSQL. I also
scripted the changing of the sa password if you need that it is:
exec sp_Password 'OriginalPasswordHere', 'New_passwordHere1', 'sa'
Hope that helps!|||On Mar 13, 9:13=A0am, Tonagon <tony.robe...@.marriott.com> wrote:
> On Mar 12, 7:29=A0am, "Spin" <S...@.spin.com> wrote:
> > "Zarko Jovanovic" <mind_lessIsHsAsTsEsSsPsA...@.inet.hr> wrote in message=
> >news:fr8dkh$olp$4@.sunce.iskon.hr...
> > > simple: YES !
> > How can I stop this behavior?
> It just so happens that I had to figure out how to disable this myself
> just last week. =A0I worked on SQL 2000, but hopefully this will work on
> 2005 as well.
> I put this into a .sql file and called it using osql, in this way I
> was able to scipt this change and execute it on our hundreds of
> different servers very quickly.
> However, you could just drop this into query analyzer (built into EM
> in 2005 I recall) and run it:
> exec sp_denylogin [BUILTIN\administrators]
> That is it, that will deby local admin access to MSSQL. =A0I also
> scripted the changing of the sa password if you need that it is:
> exec sp_Password 'OriginalPasswordHere', 'New_passwordHere1', 'sa'
> Hope that helps!
OH! Something to look out for though. If you deny login access to the
Windows Admin group then the SQL Agent service will stop running and
say it cannot connect. By default it will be connecting using a local
Windows Admin account. That will have to be changed (you can right
click it in EM and go to properties and change it).
I found a script for that as well, but it is rather long and I don't
even understand the whole thing. It works though! If you need that as
well I can put it in here.|||<<OH! Something to look out for though. If you deny login access to the
Windows Admin group then the SQL Agent service will stop running and
say it cannot connect. By default it will be connecting using a local
Windows Admin account. >>
Agent do not run under an Admin account by default. It will run using the account you specify in
setup, and can also change later on using SAQL Server Configuration Manager. The handling of service
account is done the same way and tools for SQL Server service as SQL Server Agent service.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Tonagon" <tony.roberts@.marriott.com> wrote in message
news:8329df77-325f-4595-b1e1-85e8830dd0c3@.y77g2000hsy.googlegroups.com...
On Mar 13, 9:13 am, Tonagon <tony.robe...@.marriott.com> wrote:
> On Mar 12, 7:29 am, "Spin" <S...@.spin.com> wrote:
> > "Zarko Jovanovic" <mind_lessIsHsAsTsEsSsPsA...@.inet.hr> wrote in message
> >news:fr8dkh$olp$4@.sunce.iskon.hr...
> > > simple: YES !
> > How can I stop this behavior?
> It just so happens that I had to figure out how to disable this myself
> just last week. I worked on SQL 2000, but hopefully this will work on
> 2005 as well.
> I put this into a .sql file and called it using osql, in this way I
> was able to scipt this change and execute it on our hundreds of
> different servers very quickly.
> However, you could just drop this into query analyzer (built into EM
> in 2005 I recall) and run it:
> exec sp_denylogin [BUILTIN\administrators]
> That is it, that will deby local admin access to MSSQL. I also
> scripted the changing of the sa password if you need that it is:
> exec sp_Password 'OriginalPasswordHere', 'New_passwordHere1', 'sa'
> Hope that helps!
OH! Something to look out for though. If you deny login access to the
Windows Admin group then the SQL Agent service will stop running and
say it cannot connect. By default it will be connecting using a local
Windows Admin account. That will have to be changed (you can right
click it in EM and go to properties and change it).
I found a script for that as well, but it is rather long and I don't
even understand the whole thing. It works though! If you need that as
well I can put it in here.|||On Mar 14, 2:37=A0pm, "Tibor Karaszi"
<tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
> <<OH! Something to look out for though. =A0If you deny login access to the=
> Windows Admin group then the SQL Agent service will stop running and
> say it cannot connect. =A0By default it will be connecting using a local
> Windows Admin account. >>
> Agent do not run under an Admin account by default. It will run using the =account you specify in
> setup, and can also change later on using SAQL Server Configuration Manage=r. The handling of service
> account is done the same way and tools for SQL Server service as SQL Serve=r Agent service.
> --
> Tibor Karaszi, SQL Server MVPhttp://www.karaszi.com/sqlserver/default.asph=
ttp://sqlblog.com/blogs/tibor_karaszi
> "Tonagon" <tony.robe...@.marriott.com> wrote in message
> news:8329df77-325f-4595-b1e1-85e8830dd0c3@.y77g2000hsy.googlegroups.com...
> On Mar 13, 9:13 am, Tonagon <tony.robe...@.marriott.com> wrote:
>
>
> > On Mar 12, 7:29 am, "Spin" <S...@.spin.com> wrote:
> > > "Zarko Jovanovic" <mind_lessIsHsAsTsEsSsPsA...@.inet.hr> wrote in messa=ge
> > >news:fr8dkh$olp$4@.sunce.iskon.hr...
> > > > simple: YES !
> > > How can I stop this behavior?
> > It just so happens that I had to figure out how to disable this myself
> > just last week. I worked on SQL 2000, but hopefully this will work on
> > 2005 as well.
> > I put this into a .sql file and called it using osql, in this way I
> > was able to scipt this change and execute it on our hundreds of
> > different servers very quickly.
> > However, you could just drop this into query analyzer (built into EM
> > in 2005 I recall) and run it:
> > exec sp_denylogin [BUILTIN\administrators]
> > That is it, that will deby local admin access to MSSQL. I also
> > scripted the changing of the sa password if you need that it is:
> > exec sp_Password 'OriginalPasswordHere', 'New_passwordHere1', 'sa'
> > Hope that helps!
> OH! Something to look out for though. =A0If you deny login access to the
> Windows Admin group then the SQL Agent service will stop running and
> say it cannot connect. =A0By default it will be connecting using a local
> Windows Admin account. =A0That will have to be changed (you can right
> click it in EM and go to properties and change it).
> I found a script for that as well, but it is rather long and I don't
> even understand the whole thing. =A0It works though! If you need that as
> well I can put it in here.- Hide quoted text -
> - Show quoted text -
Thanks for the clarification Tibor! I thought it was by default
simply because I see it that way on all our systems, but since they
were all installed the same way that only means it is by default for
me, not for the actual software. I guess he wasn't concerned about
that though since there was no response.
Then againk, I am more then a little slow to check things myself.|||<< Thanks for the clarification Tibor! I thought it was by default
simply because I see it that way on all our systems, but since they
were all installed the same way that only means it is by default for
me, not for the actual software. >>
that is what I thought. The main reason I replied was just to not leave this thread hanging with a
statement what can be read as the product being "not as secure" by default. :-)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Tonagon" <tony.roberts@.marriott.com> wrote in message
news:aedb14e4-8b3a-4d62-89f1-95fae5eaf3ee@.p73g2000hsd.googlegroups.com...
On Mar 14, 2:37 pm, "Tibor Karaszi"
<tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
> <<OH! Something to look out for though. If you deny login access to the
> Windows Admin group then the SQL Agent service will stop running and
> say it cannot connect. By default it will be connecting using a local
> Windows Admin account. >>
> Agent do not run under an Admin account by default. It will run using the account you specify in
> setup, and can also change later on using SAQL Server Configuration Manager. The handling of
> service
> account is done the same way and tools for SQL Server service as SQL Server Agent service.
> --
> Tibor Karaszi, SQL Server
> MVPhttp://www.karaszi.com/sqlserver/default.asphttp://sqlblog.com/blogs/tibor_karaszi
> "Tonagon" <tony.robe...@.marriott.com> wrote in message
> news:8329df77-325f-4595-b1e1-85e8830dd0c3@.y77g2000hsy.googlegroups.com...
> On Mar 13, 9:13 am, Tonagon <tony.robe...@.marriott.com> wrote:
>
>
> > On Mar 12, 7:29 am, "Spin" <S...@.spin.com> wrote:
> > > "Zarko Jovanovic" <mind_lessIsHsAsTsEsSsPsA...@.inet.hr> wrote in message
> > >news:fr8dkh$olp$4@.sunce.iskon.hr...
> > > > simple: YES !
> > > How can I stop this behavior?
> > It just so happens that I had to figure out how to disable this myself
> > just last week. I worked on SQL 2000, but hopefully this will work on
> > 2005 as well.
> > I put this into a .sql file and called it using osql, in this way I
> > was able to scipt this change and execute it on our hundreds of
> > different servers very quickly.
> > However, you could just drop this into query analyzer (built into EM
> > in 2005 I recall) and run it:
> > exec sp_denylogin [BUILTIN\administrators]
> > That is it, that will deby local admin access to MSSQL. I also
> > scripted the changing of the sa password if you need that it is:
> > exec sp_Password 'OriginalPasswordHere', 'New_passwordHere1', 'sa'
> > Hope that helps!
> OH! Something to look out for though. If you deny login access to the
> Windows Admin group then the SQL Agent service will stop running and
> say it cannot connect. By default it will be connecting using a local
> Windows Admin account. That will have to be changed (you can right
> click it in EM and go to properties and change it).
> I found a script for that as well, but it is rather long and I don't
> even understand the whole thing. It works though! If you need that as
> well I can put it in here.- Hide quoted text -
> - Show quoted text -
Thanks for the clarification Tibor! I thought it was by default
simply because I see it that way on all our systems, but since they
were all installed the same way that only means it is by default for
me, not for the actual software. I guess he wasn't concerned about
that though since there was no response.
Then againk, I am more then a little slow to check things myself.|||I haven't seen anyone mention those pesky default local accounts that set
themselves up as sysadmin.
%servername%\SQLServer2005SQLAgentUser$%servername%$MSSQLSERVER
%servername%\SQLServer2005MSSQLUser$%servername%$MSSQLSERVER
It seems that the default setup puts the service account into the later. All
anyone with enough power would have to do is drop an account into one of
these and presto! What are all of the implications of taking these roles out
of the picture (or at least out of admin)? Where is the best documentation
for fixing this?
"Tibor Karaszi" wrote:
> << Thanks for the clarification Tibor! I thought it was by default
> simply because I see it that way on all our systems, but since they
> were all installed the same way that only means it is by default for
> me, not for the actual software. >>
> that is what I thought. The main reason I replied was just to not leave this thread hanging with a
> statement what can be read as the product being "not as secure" by default. :-)
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Tonagon" <tony.roberts@.marriott.com> wrote in message
> news:aedb14e4-8b3a-4d62-89f1-95fae5eaf3ee@.p73g2000hsd.googlegroups.com...
> On Mar 14, 2:37 pm, "Tibor Karaszi"
> <tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
> > <<OH! Something to look out for though. If you deny login access to the
> > Windows Admin group then the SQL Agent service will stop running and
> > say it cannot connect. By default it will be connecting using a local
> > Windows Admin account. >>
> >
> > Agent do not run under an Admin account by default. It will run using the account you specify in
> > setup, and can also change later on using SAQL Server Configuration Manager. The handling of
> > service
> > account is done the same way and tools for SQL Server service as SQL Server Agent service.
> >
> > --
> > Tibor Karaszi, SQL Server
> > MVPhttp://www.karaszi.com/sqlserver/default.asphttp://sqlblog.com/blogs/tibor_karaszi
> >
> > "Tonagon" <tony.robe...@.marriott.com> wrote in message
> >
> > news:8329df77-325f-4595-b1e1-85e8830dd0c3@.y77g2000hsy.googlegroups.com...
> > On Mar 13, 9:13 am, Tonagon <tony.robe...@.marriott.com> wrote:
> >
> >
> >
> >
> >
> > > On Mar 12, 7:29 am, "Spin" <S...@.spin.com> wrote:
> >
> > > > "Zarko Jovanovic" <mind_lessIsHsAsTsEsSsPsA...@.inet.hr> wrote in message
> >
> > > >news:fr8dkh$olp$4@.sunce.iskon.hr...
> >
> > > > > simple: YES !
> >
> > > > How can I stop this behavior?
> >
> > > It just so happens that I had to figure out how to disable this myself
> > > just last week. I worked on SQL 2000, but hopefully this will work on
> > > 2005 as well.
> > > I put this into a .sql file and called it using osql, in this way I
> > > was able to scipt this change and execute it on our hundreds of
> > > different servers very quickly.
> > > However, you could just drop this into query analyzer (built into EM
> > > in 2005 I recall) and run it:
> >
> > > exec sp_denylogin [BUILTIN\administrators]
> >
> > > That is it, that will deby local admin access to MSSQL. I also
> > > scripted the changing of the sa password if you need that it is:
> > > exec sp_Password 'OriginalPasswordHere', 'New_passwordHere1', 'sa'
> >
> > > Hope that helps!
> >
> > OH! Something to look out for though. If you deny login access to the
> > Windows Admin group then the SQL Agent service will stop running and
> > say it cannot connect. By default it will be connecting using a local
> > Windows Admin account. That will have to be changed (you can right
> > click it in EM and go to properties and change it).
> > I found a script for that as well, but it is rather long and I don't
> > even understand the whole thing. It works though! If you need that as
> > well I can put it in here.- Hide quoted text -
> >
> > - Show quoted text -
> Thanks for the clarification Tibor! I thought it was by default
> simply because I see it that way on all our systems, but since they
> were all installed the same way that only means it is by default for
> me, not for the actual software. I guess he wasn't concerned about
> that though since there was no response.
> Then againk, I am more then a little slow to check things myself.
>|||err groups. I meant groups instead of accounts.
"Cory" wrote:
> I haven't seen anyone mention those pesky default local accounts that set
> themselves up as sysadmin.
> %servername%\SQLServer2005SQLAgentUser$%servername%$MSSQLSERVER
> %servername%\SQLServer2005MSSQLUser$%servername%$MSSQLSERVER
> It seems that the default setup puts the service account into the later. All
> anyone with enough power would have to do is drop an account into one of
> these and presto! What are all of the implications of taking these roles out
> of the picture (or at least out of admin)? Where is the best documentation
> for fixing this?
>
> "Tibor Karaszi" wrote:
> > << Thanks for the clarification Tibor! I thought it was by default
> > simply because I see it that way on all our systems, but since they
> > were all installed the same way that only means it is by default for
> > me, not for the actual software. >>
> >
> > that is what I thought. The main reason I replied was just to not leave this thread hanging with a
> > statement what can be read as the product being "not as secure" by default. :-)
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > http://www.karaszi.com/sqlserver/default.asp
> > http://sqlblog.com/blogs/tibor_karaszi
> >
> >
> > "Tonagon" <tony.roberts@.marriott.com> wrote in message
> > news:aedb14e4-8b3a-4d62-89f1-95fae5eaf3ee@.p73g2000hsd.googlegroups.com...
> > On Mar 14, 2:37 pm, "Tibor Karaszi"
> > <tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
> > > <<OH! Something to look out for though. If you deny login access to the
> > > Windows Admin group then the SQL Agent service will stop running and
> > > say it cannot connect. By default it will be connecting using a local
> > > Windows Admin account. >>
> > >
> > > Agent do not run under an Admin account by default. It will run using the account you specify in
> > > setup, and can also change later on using SAQL Server Configuration Manager. The handling of
> > > service
> > > account is done the same way and tools for SQL Server service as SQL Server Agent service.
> > >
> > > --
> > > Tibor Karaszi, SQL Server
> > > MVPhttp://www.karaszi.com/sqlserver/default.asphttp://sqlblog.com/blogs/tibor_karaszi
> > >
> > > "Tonagon" <tony.robe...@.marriott.com> wrote in message
> > >
> > > news:8329df77-325f-4595-b1e1-85e8830dd0c3@.y77g2000hsy.googlegroups.com...
> > > On Mar 13, 9:13 am, Tonagon <tony.robe...@.marriott.com> wrote:
> > >
> > >
> > >
> > >
> > >
> > > > On Mar 12, 7:29 am, "Spin" <S...@.spin.com> wrote:
> > >
> > > > > "Zarko Jovanovic" <mind_lessIsHsAsTsEsSsPsA...@.inet.hr> wrote in message
> > >
> > > > >news:fr8dkh$olp$4@.sunce.iskon.hr...
> > >
> > > > > > simple: YES !
> > >
> > > > > How can I stop this behavior?
> > >
> > > > It just so happens that I had to figure out how to disable this myself
> > > > just last week. I worked on SQL 2000, but hopefully this will work on
> > > > 2005 as well.
> > > > I put this into a .sql file and called it using osql, in this way I
> > > > was able to scipt this change and execute it on our hundreds of
> > > > different servers very quickly.
> > > > However, you could just drop this into query analyzer (built into EM
> > > > in 2005 I recall) and run it:
> > >
> > > > exec sp_denylogin [BUILTIN\administrators]
> > >
> > > > That is it, that will deby local admin access to MSSQL. I also
> > > > scripted the changing of the sa password if you need that it is:
> > > > exec sp_Password 'OriginalPasswordHere', 'New_passwordHere1', 'sa'
> > >
> > > > Hope that helps!
> > >
> > > OH! Something to look out for though. If you deny login access to the
> > > Windows Admin group then the SQL Agent service will stop running and
> > > say it cannot connect. By default it will be connecting using a local
> > > Windows Admin account. That will have to be changed (you can right
> > > click it in EM and go to properties and change it).
> > > I found a script for that as well, but it is rather long and I don't
> > > even understand the whole thing. It works though! If you need that as
> > > well I can put it in here.- Hide quoted text -
> > >
> > > - Show quoted text -
> >
> > Thanks for the clarification Tibor! I thought it was by default
> > simply because I see it that way on all our systems, but since they
> > were all installed the same way that only means it is by default for
> > me, not for the actual software. I guess he wasn't concerned about
> > that though since there was no response.
> > Then againk, I am more then a little slow to check things myself.
> >
Is it true that local administrators on a system with SQL Server 2005
installed have an implied 'sysadmin' right to SQL Server?
--
SpinSpin wrote:
> Gurus,
> Is it true that local administrators on a system with SQL Server 2005
> installed have an implied 'sysadmin' right to SQL Server?
simple: YES !|||"Zarko Jovanovic" <mind_lessIsHsAsTsEsSsPsAsMs@.inet.hr> wrote in message
news:fr8dkh$olp$4@.sunce.iskon.hr...
> simple: YES !
How can I stop this behavior?|||Spin wrote:
> "Zarko Jovanovic" <mind_lessIsHsAsTsEsSsPsAsMs@.inet.hr> wrote in message
> news:fr8dkh$olp$4@.sunce.iskon.hr...
>> simple: YES !
> How can I stop this behavior?
>
1 first, be sure you give some windows account (yours maybe) sysadmin
rights on sql server!!!
2 then, check 1 again!!!
3 then, remove BUILTIN\administrators login from logins on SQL server|||Unless the OS is Vista+SQL Server2005 SP2 (may be also WinServer2008+SQL
Server2005SP2).
"Zarko Jovanovic" <mind_lessIsHsAsTsEsSsPsAsMs@.inet.hr> wrote in message
news:fr8dkh$olp$4@.sunce.iskon.hr...
> Spin wrote:
>> Gurus,
>> Is it true that local administrators on a system with SQL Server 2005
>> installed have an implied 'sysadmin' right to SQL Server?
> simple: YES !|||On Mar 12, 7:29=A0am, "Spin" <S...@.spin.com> wrote:
> "Zarko Jovanovic" <mind_lessIsHsAsTsEsSsPsA...@.inet.hr> wrote in message
> news:fr8dkh$olp$4@.sunce.iskon.hr...
> > simple: YES !
> How can I stop this behavior?
It just so happens that I had to figure out how to disable this myself
just last week. I worked on SQL 2000, but hopefully this will work on
2005 as well.
I put this into a .sql file and called it using osql, in this way I
was able to scipt this change and execute it on our hundreds of
different servers very quickly.
However, you could just drop this into query analyzer (built into EM
in 2005 I recall) and run it:
exec sp_denylogin [BUILTIN\administrators]
That is it, that will deby local admin access to MSSQL. I also
scripted the changing of the sa password if you need that it is:
exec sp_Password 'OriginalPasswordHere', 'New_passwordHere1', 'sa'
Hope that helps!|||On Mar 13, 9:13=A0am, Tonagon <tony.robe...@.marriott.com> wrote:
> On Mar 12, 7:29=A0am, "Spin" <S...@.spin.com> wrote:
> > "Zarko Jovanovic" <mind_lessIsHsAsTsEsSsPsA...@.inet.hr> wrote in message=
> >news:fr8dkh$olp$4@.sunce.iskon.hr...
> > > simple: YES !
> > How can I stop this behavior?
> It just so happens that I had to figure out how to disable this myself
> just last week. =A0I worked on SQL 2000, but hopefully this will work on
> 2005 as well.
> I put this into a .sql file and called it using osql, in this way I
> was able to scipt this change and execute it on our hundreds of
> different servers very quickly.
> However, you could just drop this into query analyzer (built into EM
> in 2005 I recall) and run it:
> exec sp_denylogin [BUILTIN\administrators]
> That is it, that will deby local admin access to MSSQL. =A0I also
> scripted the changing of the sa password if you need that it is:
> exec sp_Password 'OriginalPasswordHere', 'New_passwordHere1', 'sa'
> Hope that helps!
OH! Something to look out for though. If you deny login access to the
Windows Admin group then the SQL Agent service will stop running and
say it cannot connect. By default it will be connecting using a local
Windows Admin account. That will have to be changed (you can right
click it in EM and go to properties and change it).
I found a script for that as well, but it is rather long and I don't
even understand the whole thing. It works though! If you need that as
well I can put it in here.|||<<OH! Something to look out for though. If you deny login access to the
Windows Admin group then the SQL Agent service will stop running and
say it cannot connect. By default it will be connecting using a local
Windows Admin account. >>
Agent do not run under an Admin account by default. It will run using the account you specify in
setup, and can also change later on using SAQL Server Configuration Manager. The handling of service
account is done the same way and tools for SQL Server service as SQL Server Agent service.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Tonagon" <tony.roberts@.marriott.com> wrote in message
news:8329df77-325f-4595-b1e1-85e8830dd0c3@.y77g2000hsy.googlegroups.com...
On Mar 13, 9:13 am, Tonagon <tony.robe...@.marriott.com> wrote:
> On Mar 12, 7:29 am, "Spin" <S...@.spin.com> wrote:
> > "Zarko Jovanovic" <mind_lessIsHsAsTsEsSsPsA...@.inet.hr> wrote in message
> >news:fr8dkh$olp$4@.sunce.iskon.hr...
> > > simple: YES !
> > How can I stop this behavior?
> It just so happens that I had to figure out how to disable this myself
> just last week. I worked on SQL 2000, but hopefully this will work on
> 2005 as well.
> I put this into a .sql file and called it using osql, in this way I
> was able to scipt this change and execute it on our hundreds of
> different servers very quickly.
> However, you could just drop this into query analyzer (built into EM
> in 2005 I recall) and run it:
> exec sp_denylogin [BUILTIN\administrators]
> That is it, that will deby local admin access to MSSQL. I also
> scripted the changing of the sa password if you need that it is:
> exec sp_Password 'OriginalPasswordHere', 'New_passwordHere1', 'sa'
> Hope that helps!
OH! Something to look out for though. If you deny login access to the
Windows Admin group then the SQL Agent service will stop running and
say it cannot connect. By default it will be connecting using a local
Windows Admin account. That will have to be changed (you can right
click it in EM and go to properties and change it).
I found a script for that as well, but it is rather long and I don't
even understand the whole thing. It works though! If you need that as
well I can put it in here.|||On Mar 14, 2:37=A0pm, "Tibor Karaszi"
<tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
> <<OH! Something to look out for though. =A0If you deny login access to the=
> Windows Admin group then the SQL Agent service will stop running and
> say it cannot connect. =A0By default it will be connecting using a local
> Windows Admin account. >>
> Agent do not run under an Admin account by default. It will run using the =account you specify in
> setup, and can also change later on using SAQL Server Configuration Manage=r. The handling of service
> account is done the same way and tools for SQL Server service as SQL Serve=r Agent service.
> --
> Tibor Karaszi, SQL Server MVPhttp://www.karaszi.com/sqlserver/default.asph=
ttp://sqlblog.com/blogs/tibor_karaszi
> "Tonagon" <tony.robe...@.marriott.com> wrote in message
> news:8329df77-325f-4595-b1e1-85e8830dd0c3@.y77g2000hsy.googlegroups.com...
> On Mar 13, 9:13 am, Tonagon <tony.robe...@.marriott.com> wrote:
>
>
> > On Mar 12, 7:29 am, "Spin" <S...@.spin.com> wrote:
> > > "Zarko Jovanovic" <mind_lessIsHsAsTsEsSsPsA...@.inet.hr> wrote in messa=ge
> > >news:fr8dkh$olp$4@.sunce.iskon.hr...
> > > > simple: YES !
> > > How can I stop this behavior?
> > It just so happens that I had to figure out how to disable this myself
> > just last week. I worked on SQL 2000, but hopefully this will work on
> > 2005 as well.
> > I put this into a .sql file and called it using osql, in this way I
> > was able to scipt this change and execute it on our hundreds of
> > different servers very quickly.
> > However, you could just drop this into query analyzer (built into EM
> > in 2005 I recall) and run it:
> > exec sp_denylogin [BUILTIN\administrators]
> > That is it, that will deby local admin access to MSSQL. I also
> > scripted the changing of the sa password if you need that it is:
> > exec sp_Password 'OriginalPasswordHere', 'New_passwordHere1', 'sa'
> > Hope that helps!
> OH! Something to look out for though. =A0If you deny login access to the
> Windows Admin group then the SQL Agent service will stop running and
> say it cannot connect. =A0By default it will be connecting using a local
> Windows Admin account. =A0That will have to be changed (you can right
> click it in EM and go to properties and change it).
> I found a script for that as well, but it is rather long and I don't
> even understand the whole thing. =A0It works though! If you need that as
> well I can put it in here.- Hide quoted text -
> - Show quoted text -
Thanks for the clarification Tibor! I thought it was by default
simply because I see it that way on all our systems, but since they
were all installed the same way that only means it is by default for
me, not for the actual software. I guess he wasn't concerned about
that though since there was no response.
Then againk, I am more then a little slow to check things myself.|||<< Thanks for the clarification Tibor! I thought it was by default
simply because I see it that way on all our systems, but since they
were all installed the same way that only means it is by default for
me, not for the actual software. >>
that is what I thought. The main reason I replied was just to not leave this thread hanging with a
statement what can be read as the product being "not as secure" by default. :-)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Tonagon" <tony.roberts@.marriott.com> wrote in message
news:aedb14e4-8b3a-4d62-89f1-95fae5eaf3ee@.p73g2000hsd.googlegroups.com...
On Mar 14, 2:37 pm, "Tibor Karaszi"
<tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
> <<OH! Something to look out for though. If you deny login access to the
> Windows Admin group then the SQL Agent service will stop running and
> say it cannot connect. By default it will be connecting using a local
> Windows Admin account. >>
> Agent do not run under an Admin account by default. It will run using the account you specify in
> setup, and can also change later on using SAQL Server Configuration Manager. The handling of
> service
> account is done the same way and tools for SQL Server service as SQL Server Agent service.
> --
> Tibor Karaszi, SQL Server
> MVPhttp://www.karaszi.com/sqlserver/default.asphttp://sqlblog.com/blogs/tibor_karaszi
> "Tonagon" <tony.robe...@.marriott.com> wrote in message
> news:8329df77-325f-4595-b1e1-85e8830dd0c3@.y77g2000hsy.googlegroups.com...
> On Mar 13, 9:13 am, Tonagon <tony.robe...@.marriott.com> wrote:
>
>
> > On Mar 12, 7:29 am, "Spin" <S...@.spin.com> wrote:
> > > "Zarko Jovanovic" <mind_lessIsHsAsTsEsSsPsA...@.inet.hr> wrote in message
> > >news:fr8dkh$olp$4@.sunce.iskon.hr...
> > > > simple: YES !
> > > How can I stop this behavior?
> > It just so happens that I had to figure out how to disable this myself
> > just last week. I worked on SQL 2000, but hopefully this will work on
> > 2005 as well.
> > I put this into a .sql file and called it using osql, in this way I
> > was able to scipt this change and execute it on our hundreds of
> > different servers very quickly.
> > However, you could just drop this into query analyzer (built into EM
> > in 2005 I recall) and run it:
> > exec sp_denylogin [BUILTIN\administrators]
> > That is it, that will deby local admin access to MSSQL. I also
> > scripted the changing of the sa password if you need that it is:
> > exec sp_Password 'OriginalPasswordHere', 'New_passwordHere1', 'sa'
> > Hope that helps!
> OH! Something to look out for though. If you deny login access to the
> Windows Admin group then the SQL Agent service will stop running and
> say it cannot connect. By default it will be connecting using a local
> Windows Admin account. That will have to be changed (you can right
> click it in EM and go to properties and change it).
> I found a script for that as well, but it is rather long and I don't
> even understand the whole thing. It works though! If you need that as
> well I can put it in here.- Hide quoted text -
> - Show quoted text -
Thanks for the clarification Tibor! I thought it was by default
simply because I see it that way on all our systems, but since they
were all installed the same way that only means it is by default for
me, not for the actual software. I guess he wasn't concerned about
that though since there was no response.
Then againk, I am more then a little slow to check things myself.|||I haven't seen anyone mention those pesky default local accounts that set
themselves up as sysadmin.
%servername%\SQLServer2005SQLAgentUser$%servername%$MSSQLSERVER
%servername%\SQLServer2005MSSQLUser$%servername%$MSSQLSERVER
It seems that the default setup puts the service account into the later. All
anyone with enough power would have to do is drop an account into one of
these and presto! What are all of the implications of taking these roles out
of the picture (or at least out of admin)? Where is the best documentation
for fixing this?
"Tibor Karaszi" wrote:
> << Thanks for the clarification Tibor! I thought it was by default
> simply because I see it that way on all our systems, but since they
> were all installed the same way that only means it is by default for
> me, not for the actual software. >>
> that is what I thought. The main reason I replied was just to not leave this thread hanging with a
> statement what can be read as the product being "not as secure" by default. :-)
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Tonagon" <tony.roberts@.marriott.com> wrote in message
> news:aedb14e4-8b3a-4d62-89f1-95fae5eaf3ee@.p73g2000hsd.googlegroups.com...
> On Mar 14, 2:37 pm, "Tibor Karaszi"
> <tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
> > <<OH! Something to look out for though. If you deny login access to the
> > Windows Admin group then the SQL Agent service will stop running and
> > say it cannot connect. By default it will be connecting using a local
> > Windows Admin account. >>
> >
> > Agent do not run under an Admin account by default. It will run using the account you specify in
> > setup, and can also change later on using SAQL Server Configuration Manager. The handling of
> > service
> > account is done the same way and tools for SQL Server service as SQL Server Agent service.
> >
> > --
> > Tibor Karaszi, SQL Server
> > MVPhttp://www.karaszi.com/sqlserver/default.asphttp://sqlblog.com/blogs/tibor_karaszi
> >
> > "Tonagon" <tony.robe...@.marriott.com> wrote in message
> >
> > news:8329df77-325f-4595-b1e1-85e8830dd0c3@.y77g2000hsy.googlegroups.com...
> > On Mar 13, 9:13 am, Tonagon <tony.robe...@.marriott.com> wrote:
> >
> >
> >
> >
> >
> > > On Mar 12, 7:29 am, "Spin" <S...@.spin.com> wrote:
> >
> > > > "Zarko Jovanovic" <mind_lessIsHsAsTsEsSsPsA...@.inet.hr> wrote in message
> >
> > > >news:fr8dkh$olp$4@.sunce.iskon.hr...
> >
> > > > > simple: YES !
> >
> > > > How can I stop this behavior?
> >
> > > It just so happens that I had to figure out how to disable this myself
> > > just last week. I worked on SQL 2000, but hopefully this will work on
> > > 2005 as well.
> > > I put this into a .sql file and called it using osql, in this way I
> > > was able to scipt this change and execute it on our hundreds of
> > > different servers very quickly.
> > > However, you could just drop this into query analyzer (built into EM
> > > in 2005 I recall) and run it:
> >
> > > exec sp_denylogin [BUILTIN\administrators]
> >
> > > That is it, that will deby local admin access to MSSQL. I also
> > > scripted the changing of the sa password if you need that it is:
> > > exec sp_Password 'OriginalPasswordHere', 'New_passwordHere1', 'sa'
> >
> > > Hope that helps!
> >
> > OH! Something to look out for though. If you deny login access to the
> > Windows Admin group then the SQL Agent service will stop running and
> > say it cannot connect. By default it will be connecting using a local
> > Windows Admin account. That will have to be changed (you can right
> > click it in EM and go to properties and change it).
> > I found a script for that as well, but it is rather long and I don't
> > even understand the whole thing. It works though! If you need that as
> > well I can put it in here.- Hide quoted text -
> >
> > - Show quoted text -
> Thanks for the clarification Tibor! I thought it was by default
> simply because I see it that way on all our systems, but since they
> were all installed the same way that only means it is by default for
> me, not for the actual software. I guess he wasn't concerned about
> that though since there was no response.
> Then againk, I am more then a little slow to check things myself.
>|||err groups. I meant groups instead of accounts.
"Cory" wrote:
> I haven't seen anyone mention those pesky default local accounts that set
> themselves up as sysadmin.
> %servername%\SQLServer2005SQLAgentUser$%servername%$MSSQLSERVER
> %servername%\SQLServer2005MSSQLUser$%servername%$MSSQLSERVER
> It seems that the default setup puts the service account into the later. All
> anyone with enough power would have to do is drop an account into one of
> these and presto! What are all of the implications of taking these roles out
> of the picture (or at least out of admin)? Where is the best documentation
> for fixing this?
>
> "Tibor Karaszi" wrote:
> > << Thanks for the clarification Tibor! I thought it was by default
> > simply because I see it that way on all our systems, but since they
> > were all installed the same way that only means it is by default for
> > me, not for the actual software. >>
> >
> > that is what I thought. The main reason I replied was just to not leave this thread hanging with a
> > statement what can be read as the product being "not as secure" by default. :-)
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > http://www.karaszi.com/sqlserver/default.asp
> > http://sqlblog.com/blogs/tibor_karaszi
> >
> >
> > "Tonagon" <tony.roberts@.marriott.com> wrote in message
> > news:aedb14e4-8b3a-4d62-89f1-95fae5eaf3ee@.p73g2000hsd.googlegroups.com...
> > On Mar 14, 2:37 pm, "Tibor Karaszi"
> > <tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
> > > <<OH! Something to look out for though. If you deny login access to the
> > > Windows Admin group then the SQL Agent service will stop running and
> > > say it cannot connect. By default it will be connecting using a local
> > > Windows Admin account. >>
> > >
> > > Agent do not run under an Admin account by default. It will run using the account you specify in
> > > setup, and can also change later on using SAQL Server Configuration Manager. The handling of
> > > service
> > > account is done the same way and tools for SQL Server service as SQL Server Agent service.
> > >
> > > --
> > > Tibor Karaszi, SQL Server
> > > MVPhttp://www.karaszi.com/sqlserver/default.asphttp://sqlblog.com/blogs/tibor_karaszi
> > >
> > > "Tonagon" <tony.robe...@.marriott.com> wrote in message
> > >
> > > news:8329df77-325f-4595-b1e1-85e8830dd0c3@.y77g2000hsy.googlegroups.com...
> > > On Mar 13, 9:13 am, Tonagon <tony.robe...@.marriott.com> wrote:
> > >
> > >
> > >
> > >
> > >
> > > > On Mar 12, 7:29 am, "Spin" <S...@.spin.com> wrote:
> > >
> > > > > "Zarko Jovanovic" <mind_lessIsHsAsTsEsSsPsA...@.inet.hr> wrote in message
> > >
> > > > >news:fr8dkh$olp$4@.sunce.iskon.hr...
> > >
> > > > > > simple: YES !
> > >
> > > > > How can I stop this behavior?
> > >
> > > > It just so happens that I had to figure out how to disable this myself
> > > > just last week. I worked on SQL 2000, but hopefully this will work on
> > > > 2005 as well.
> > > > I put this into a .sql file and called it using osql, in this way I
> > > > was able to scipt this change and execute it on our hundreds of
> > > > different servers very quickly.
> > > > However, you could just drop this into query analyzer (built into EM
> > > > in 2005 I recall) and run it:
> > >
> > > > exec sp_denylogin [BUILTIN\administrators]
> > >
> > > > That is it, that will deby local admin access to MSSQL. I also
> > > > scripted the changing of the sa password if you need that it is:
> > > > exec sp_Password 'OriginalPasswordHere', 'New_passwordHere1', 'sa'
> > >
> > > > Hope that helps!
> > >
> > > OH! Something to look out for though. If you deny login access to the
> > > Windows Admin group then the SQL Agent service will stop running and
> > > say it cannot connect. By default it will be connecting using a local
> > > Windows Admin account. That will have to be changed (you can right
> > > click it in EM and go to properties and change it).
> > > I found a script for that as well, but it is rather long and I don't
> > > even understand the whole thing. It works though! If you need that as
> > > well I can put it in here.- Hide quoted text -
> > >
> > > - Show quoted text -
> >
> > Thanks for the clarification Tibor! I thought it was by default
> > simply because I see it that way on all our systems, but since they
> > were all installed the same way that only means it is by default for
> > me, not for the actual software. I guess he wasn't concerned about
> > that though since there was no response.
> > Then againk, I am more then a little slow to check things myself.
> >
Friday, February 17, 2012
distributed transaction issue
Dear gurus,
Your help is greatly appreciated...
My environment:
SQL Server 2K5 EE SP2 build# 3154 on Windows 2K3 SP1
I have three servers:
Server_1, Server_2, and Server_3
My issues:
I have the issue between Server_1 and Server_2 when I start a distributed
transaction (all linked server are set up properly), like:
on Server_1:
begin distributed tran
select * from Server_2.master.sys.databases
commit tran
I will receive the error:
OLE DB provider "SQLNCLI" for linked server "Server_2" returned message "No
transaction is active.".
Msg 7391, Level 16, State 2, Line 2
The operation could not be performed because OLE DB provider "SQLNCLI" for
linked server "Server_2" was unable to begin a distributed transaction.
This happens on Server_2, when I run the following:
begin distributed tran
select * from Server_1.master.sys.databases
commit tran
However if I run the query on Server_3 or run against Server_3 from either
Server_1 or Server_2, no error occurs:
i.e if I run on Server_3:
begin distributed tran
select * from Server_1.master.sys.databases
select * from Server_2.master.sys.databases
commit tran
or run on either Server_1 or Server_2
begin distributed tran
select * from Server_3.master.sys.databases
commit tran
they are all successful.
What puzzles me is the fact:
Server_1 and Server_3 can have distributed transactions without problem, and
so can Server2 and Server_3, but NOT Server_1 and Server_2.
So this seems something is wrong between Server_1 and Server_2, also I can
confirm all servers are in the same domain and MS DTC is running on all
servers fine.
can some shed some lights on how come this can happen?
Thanks in advance for your time.
Kind regards,
Jeff
There are a lot of different possiblities. I would google for the latest
com+ rollup and configuring dtc through a firewall kb for starters.
Jason Massie
http://statisticsio.com
"jeff_y" <jeffrey_ya@.abc.com> wrote in message
news:OgufZO8KIHA.5360@.TK2MSFTNGP03.phx.gbl...
> Dear gurus,
> Your help is greatly appreciated...
> My environment:
> SQL Server 2K5 EE SP2 build# 3154 on Windows 2K3 SP1
> I have three servers:
> Server_1, Server_2, and Server_3
> My issues:
> I have the issue between Server_1 and Server_2 when I start a distributed
> transaction (all linked server are set up properly), like:
> on Server_1:
> begin distributed tran
> select * from Server_2.master.sys.databases
> commit tran
> I will receive the error:
> OLE DB provider "SQLNCLI" for linked server "Server_2" returned message
> "No transaction is active.".
> Msg 7391, Level 16, State 2, Line 2
> The operation could not be performed because OLE DB provider "SQLNCLI" for
> linked server "Server_2" was unable to begin a distributed transaction.
> This happens on Server_2, when I run the following:
> begin distributed tran
> select * from Server_1.master.sys.databases
> commit tran
> However if I run the query on Server_3 or run against Server_3 from either
> Server_1 or Server_2, no error occurs:
> i.e if I run on Server_3:
> begin distributed tran
> select * from Server_1.master.sys.databases
> select * from Server_2.master.sys.databases
> commit tran
> or run on either Server_1 or Server_2
> begin distributed tran
> select * from Server_3.master.sys.databases
> commit tran
> they are all successful.
> What puzzles me is the fact:
> Server_1 and Server_3 can have distributed transactions without problem,
> and so can Server2 and Server_3, but NOT Server_1 and Server_2.
> So this seems something is wrong between Server_1 and Server_2, also I can
> confirm all servers are in the same domain and MS DTC is running on all
> servers fine.
>
> can some shed some lights on how come this can happen?
> Thanks in advance for your time.
> Kind regards,
> Jeff
>
Your help is greatly appreciated...
My environment:
SQL Server 2K5 EE SP2 build# 3154 on Windows 2K3 SP1
I have three servers:
Server_1, Server_2, and Server_3
My issues:
I have the issue between Server_1 and Server_2 when I start a distributed
transaction (all linked server are set up properly), like:
on Server_1:
begin distributed tran
select * from Server_2.master.sys.databases
commit tran
I will receive the error:
OLE DB provider "SQLNCLI" for linked server "Server_2" returned message "No
transaction is active.".
Msg 7391, Level 16, State 2, Line 2
The operation could not be performed because OLE DB provider "SQLNCLI" for
linked server "Server_2" was unable to begin a distributed transaction.
This happens on Server_2, when I run the following:
begin distributed tran
select * from Server_1.master.sys.databases
commit tran
However if I run the query on Server_3 or run against Server_3 from either
Server_1 or Server_2, no error occurs:
i.e if I run on Server_3:
begin distributed tran
select * from Server_1.master.sys.databases
select * from Server_2.master.sys.databases
commit tran
or run on either Server_1 or Server_2
begin distributed tran
select * from Server_3.master.sys.databases
commit tran
they are all successful.
What puzzles me is the fact:
Server_1 and Server_3 can have distributed transactions without problem, and
so can Server2 and Server_3, but NOT Server_1 and Server_2.
So this seems something is wrong between Server_1 and Server_2, also I can
confirm all servers are in the same domain and MS DTC is running on all
servers fine.
can some shed some lights on how come this can happen?
Thanks in advance for your time.
Kind regards,
Jeff
There are a lot of different possiblities. I would google for the latest
com+ rollup and configuring dtc through a firewall kb for starters.
Jason Massie
http://statisticsio.com
"jeff_y" <jeffrey_ya@.abc.com> wrote in message
news:OgufZO8KIHA.5360@.TK2MSFTNGP03.phx.gbl...
> Dear gurus,
> Your help is greatly appreciated...
> My environment:
> SQL Server 2K5 EE SP2 build# 3154 on Windows 2K3 SP1
> I have three servers:
> Server_1, Server_2, and Server_3
> My issues:
> I have the issue between Server_1 and Server_2 when I start a distributed
> transaction (all linked server are set up properly), like:
> on Server_1:
> begin distributed tran
> select * from Server_2.master.sys.databases
> commit tran
> I will receive the error:
> OLE DB provider "SQLNCLI" for linked server "Server_2" returned message
> "No transaction is active.".
> Msg 7391, Level 16, State 2, Line 2
> The operation could not be performed because OLE DB provider "SQLNCLI" for
> linked server "Server_2" was unable to begin a distributed transaction.
> This happens on Server_2, when I run the following:
> begin distributed tran
> select * from Server_1.master.sys.databases
> commit tran
> However if I run the query on Server_3 or run against Server_3 from either
> Server_1 or Server_2, no error occurs:
> i.e if I run on Server_3:
> begin distributed tran
> select * from Server_1.master.sys.databases
> select * from Server_2.master.sys.databases
> commit tran
> or run on either Server_1 or Server_2
> begin distributed tran
> select * from Server_3.master.sys.databases
> commit tran
> they are all successful.
> What puzzles me is the fact:
> Server_1 and Server_3 can have distributed transactions without problem,
> and so can Server2 and Server_3, but NOT Server_1 and Server_2.
> So this seems something is wrong between Server_1 and Server_2, also I can
> confirm all servers are in the same domain and MS DTC is running on all
> servers fine.
>
> can some shed some lights on how come this can happen?
> Thanks in advance for your time.
> Kind regards,
> Jeff
>
Labels:
2k3,
2k5,
appreciated,
build,
database,
dear,
distributed,
environmentsql,
greatly,
gurus,
microsoft,
mysql,
oracle,
server,
serversserver_1,
sp1i,
sp2,
sql,
transaction,
windows
distributed transaction issue
Dear gurus,
Your help is greatly appreciated...
My environment:
SQL Server 2K5 EE SP2 build# 3154 on Windows 2K3 SP1
I have three servers:
Server_1, Server_2, and Server_3
My issues:
I have the issue between Server_1 and Server_2 when I start a distributed
transaction (all linked server are set up properly), like:
on Server_1:
begin distributed tran
select * from Server_2.master.sys.databases
commit tran
I will receive the error:
OLE DB provider "SQLNCLI" for linked server "Server_2" returned message "No
transaction is active.".
Msg 7391, Level 16, State 2, Line 2
The operation could not be performed because OLE DB provider "SQLNCLI" for
linked server "Server_2" was unable to begin a distributed transaction.
This happens on Server_2, when I run the following:
begin distributed tran
select * from Server_1.master.sys.databases
commit tran
However if I run the query on Server_3 or run against Server_3 from either
Server_1 or Server_2, no error occurs:
i.e if I run on Server_3:
begin distributed tran
select * from Server_1.master.sys.databases
select * from Server_2.master.sys.databases
commit tran
or run on either Server_1 or Server_2
begin distributed tran
select * from Server_3.master.sys.databases
commit tran
they are all successful.
What puzzles me is the fact:
Server_1 and Server_3 can have distributed transactions without problem, and
so can Server2 and Server_3, but NOT Server_1 and Server_2.
So this seems something is wrong between Server_1 and Server_2, also I can
confirm all servers are in the same domain and MS DTC is running on all
servers fine.
can some shed some lights on how come this can happen?
Thanks in advance for your time.
Kind regards,
JeffThere are a lot of different possiblities. I would google for the latest
com+ rollup and configuring dtc through a firewall kb for starters.
Jason Massie
http://statisticsio.com
"jeff_y" <jeffrey_ya@.abc.com> wrote in message
news:OgufZO8KIHA.5360@.TK2MSFTNGP03.phx.gbl...
> Dear gurus,
> Your help is greatly appreciated...
> My environment:
> SQL Server 2K5 EE SP2 build# 3154 on Windows 2K3 SP1
> I have three servers:
> Server_1, Server_2, and Server_3
> My issues:
> I have the issue between Server_1 and Server_2 when I start a distributed
> transaction (all linked server are set up properly), like:
> on Server_1:
> begin distributed tran
> select * from Server_2.master.sys.databases
> commit tran
> I will receive the error:
> OLE DB provider "SQLNCLI" for linked server "Server_2" returned message
> "No transaction is active.".
> Msg 7391, Level 16, State 2, Line 2
> The operation could not be performed because OLE DB provider "SQLNCLI" for
> linked server "Server_2" was unable to begin a distributed transaction.
> This happens on Server_2, when I run the following:
> begin distributed tran
> select * from Server_1.master.sys.databases
> commit tran
> However if I run the query on Server_3 or run against Server_3 from either
> Server_1 or Server_2, no error occurs:
> i.e if I run on Server_3:
> begin distributed tran
> select * from Server_1.master.sys.databases
> select * from Server_2.master.sys.databases
> commit tran
> or run on either Server_1 or Server_2
> begin distributed tran
> select * from Server_3.master.sys.databases
> commit tran
> they are all successful.
> What puzzles me is the fact:
> Server_1 and Server_3 can have distributed transactions without problem,
> and so can Server2 and Server_3, but NOT Server_1 and Server_2.
> So this seems something is wrong between Server_1 and Server_2, also I can
> confirm all servers are in the same domain and MS DTC is running on all
> servers fine.
>
> can some shed some lights on how come this can happen?
> Thanks in advance for your time.
> Kind regards,
> Jeff
>
Your help is greatly appreciated...
My environment:
SQL Server 2K5 EE SP2 build# 3154 on Windows 2K3 SP1
I have three servers:
Server_1, Server_2, and Server_3
My issues:
I have the issue between Server_1 and Server_2 when I start a distributed
transaction (all linked server are set up properly), like:
on Server_1:
begin distributed tran
select * from Server_2.master.sys.databases
commit tran
I will receive the error:
OLE DB provider "SQLNCLI" for linked server "Server_2" returned message "No
transaction is active.".
Msg 7391, Level 16, State 2, Line 2
The operation could not be performed because OLE DB provider "SQLNCLI" for
linked server "Server_2" was unable to begin a distributed transaction.
This happens on Server_2, when I run the following:
begin distributed tran
select * from Server_1.master.sys.databases
commit tran
However if I run the query on Server_3 or run against Server_3 from either
Server_1 or Server_2, no error occurs:
i.e if I run on Server_3:
begin distributed tran
select * from Server_1.master.sys.databases
select * from Server_2.master.sys.databases
commit tran
or run on either Server_1 or Server_2
begin distributed tran
select * from Server_3.master.sys.databases
commit tran
they are all successful.
What puzzles me is the fact:
Server_1 and Server_3 can have distributed transactions without problem, and
so can Server2 and Server_3, but NOT Server_1 and Server_2.
So this seems something is wrong between Server_1 and Server_2, also I can
confirm all servers are in the same domain and MS DTC is running on all
servers fine.
can some shed some lights on how come this can happen?
Thanks in advance for your time.
Kind regards,
JeffThere are a lot of different possiblities. I would google for the latest
com+ rollup and configuring dtc through a firewall kb for starters.
Jason Massie
http://statisticsio.com
"jeff_y" <jeffrey_ya@.abc.com> wrote in message
news:OgufZO8KIHA.5360@.TK2MSFTNGP03.phx.gbl...
> Dear gurus,
> Your help is greatly appreciated...
> My environment:
> SQL Server 2K5 EE SP2 build# 3154 on Windows 2K3 SP1
> I have three servers:
> Server_1, Server_2, and Server_3
> My issues:
> I have the issue between Server_1 and Server_2 when I start a distributed
> transaction (all linked server are set up properly), like:
> on Server_1:
> begin distributed tran
> select * from Server_2.master.sys.databases
> commit tran
> I will receive the error:
> OLE DB provider "SQLNCLI" for linked server "Server_2" returned message
> "No transaction is active.".
> Msg 7391, Level 16, State 2, Line 2
> The operation could not be performed because OLE DB provider "SQLNCLI" for
> linked server "Server_2" was unable to begin a distributed transaction.
> This happens on Server_2, when I run the following:
> begin distributed tran
> select * from Server_1.master.sys.databases
> commit tran
> However if I run the query on Server_3 or run against Server_3 from either
> Server_1 or Server_2, no error occurs:
> i.e if I run on Server_3:
> begin distributed tran
> select * from Server_1.master.sys.databases
> select * from Server_2.master.sys.databases
> commit tran
> or run on either Server_1 or Server_2
> begin distributed tran
> select * from Server_3.master.sys.databases
> commit tran
> they are all successful.
> What puzzles me is the fact:
> Server_1 and Server_3 can have distributed transactions without problem,
> and so can Server2 and Server_3, but NOT Server_1 and Server_2.
> So this seems something is wrong between Server_1 and Server_2, also I can
> confirm all servers are in the same domain and MS DTC is running on all
> servers fine.
>
> can some shed some lights on how come this can happen?
> Thanks in advance for your time.
> Kind regards,
> Jeff
>
Labels:
2k3,
2k5,
appreciated,
build,
database,
dear,
distributed,
environmentsql,
greatly,
gurus,
microsoft,
mysql,
oracle,
server,
serversserver_1,
sp1i,
sp2,
sql,
transaction,
windows
Subscribe to:
Posts (Atom)