Remove domain admins access to SQL 2005 DBs

Hello,
Could you please advise how can I remove domain admins' access to SQL
databases on SQL Server 2005? Is there any only SQL Authentication mode ?OzzY
There is BUILTIN\Administrators group undfer Security folder. You can deny
access to database engine for this group
"OzzY" <apexgore@.yahoo.com> wrote in message
news:uKGdYUt1HHA.3400@.TK2MSFTNGP03.phx.gbl...
> Hello,
> Could you please advise how can I remove domain admins' access to SQL
> databases on SQL Server 2005? Is there any only SQL Authentication mode ?
>|||Before removing the BUILTIN\Administrators, make sure you
have another account that is a sysadmin that you can use to
get into SQL Server.
There isn't a SQL Authentication only mode - mostly because
Windows authentication is more secure. But you can remove
the BUILTIN\Administrators group. That is how the domain
admins inherit access to SQL Server - the
BUILTIN\Administrators group is the local admins group on
the box. Domain admins are members of the local admins group
on the box. And that's how they get access - all through
windows group membership and the BUILTIN\Administrators or
local admins group being setup as sysadmins on SQL Server.
-Sue
On Sat, 4 Aug 2007 23:21:50 +0300, "OzzY"
<apexgore@.yahoo.com> wrote:

>Hello,
>Could you please advise how can I remove domain admins' access to SQL
>databases on SQL Server 2005? Is there any only SQL Authentication mode ?
>

Remove DC from Cluster Node

We have:

A Microsoft cluster, (SQL Failover cluster) with one node as the domain controller. The cluster was built off site and the domain name used is the same as our existing domain where we eventually need to install this cluster.

We need: (At least I think we need:)

To remove node 2 from the "cluster domain", DCPROMO node 1 and eliminate the "cluster domain". We then need to join the cluster (nodes) to the existing domain. We also need to recreate the accounts and groups used during installation.

Questions:

1) What will happen to the "domain accounts" used when installing SQL2005? (Other than they will go away. I mean what adverse impact will that have on the installation?)

2) Will I have to re-install SQL 2005?

3) Is my paranoia real or imagined? (Will Elvis live?)

Any prior experience with this would be greatly appreciated. In fact, a WAG is appreciated too.

Regards,

Mike

For cluster setup issues, please use the SQL Server Disaster Recovery and Availability forum.

Thanks
Laurentiu

Remote SSIS vs Domain\User: Access is Denied (0x80070005)

What OS permissions do I need to give a domain user to effectively connect to a remote instance of Integration Services?

I keep getting the following message:

Cannot connect to SQLDEV01
Failed to retreive data for this request.
Access is denied. (Exception from HRESULT: 0x80070005 (E_ACCESSDENIED)) (Microsoft.SqlServer.ManagedDTS)

I have already performed the Windows 2003 steps outlined in the "Eliminating the Access is Denied" error located at http://msdn2.microsoft.com/en-us/library/aa337083.aspx

I have no problem if the user is a local Administrator (go figure).

The MSDN page lacks another steps needed on W2K3 (not sure about XP) - add the account to Distributed COM Users group. (The page is being updated).|||

Yes, I have done this, and gave the Domain\User the DCOM permissions upon the MsDtsSvr object. The Distributed COM Users step is actually included on ths webpage.

Thanks for responding. Perhaps there is another step missing?

|||

JFoushee wrote:

The Distributed COM Users step is actually included on ths webpage.

Not really (if we get the same copy of http://msdn2.microsoft.com/en-us/library/aa337083.aspx).

The page talks about configuring security for MsDtsServer application, but on Windows 2003 Server and 64-bit XP machine there is another global per-machine setting: in DCOMCNFG right click My Computer, select Properties, find COM Security page and inspect both Edit Limits settings: they should allow the user to access the machine. The simplest way to do it is to add user to Distributed COM Users user group.

|||

I believe I got it to work...

One the webpage http://msdn2.microsoft.com/en-us/library/aa337083.aspx, under "To configure rights for remote users on Windows Server 2003"...

replace step 9 with "Click OK to close the dialog box."

Add a step 9.1 with the following text: "On the same Security tab, under Access Permissions, select Customize, then click Edit to open the Access Permission dialog box."

Add a step 9.2 with the following text: "In the Access Permission dialog box, add or delete users, and assign the appropriate permissions to the appropriate users and groups. The available permissions are Local Access, and Remote Access. The easiest is to add the local DCOM Distributed Users group. "

Add a step 9.3 with the following text: "Click OK to close the dialog box. Close the MMC snap-in."

Step 10 stays as-is: "Restart the Integration Services service."

|||

Thanks a lot.

Finally I can connect to SSIS.

Remote SSIS vs Domain\User: Access is Denied (0x80070005)

What OS permissions do I need to give a domain user to effectively connect to a remote instance of Integration Services?

I keep getting the following message:

Cannot connect to SQLDEV01
Failed to retreive data for this request.
Access is denied. (Exception from HRESULT: 0x80070005 (E_ACCESSDENIED)) (Microsoft.SqlServer.ManagedDTS)

I have already performed the Windows 2003 steps outlined in the "Eliminating the Access is Denied" error located at http://msdn2.microsoft.com/en-us/library/aa337083.aspx

I have no problem if the user is a local Administrator (go figure).

The MSDN page lacks another steps needed on W2K3 (not sure about XP) - add the account to Distributed COM Users group. (The page is being updated).|||

Yes, I have done this, and gave the Domain\User the DCOM permissions upon the MsDtsSvr object. The Distributed COM Users step is actually included on ths webpage.

Thanks for responding. Perhaps there is another step missing?

|||

JFoushee wrote:

The Distributed COM Users step is actually included on ths webpage.

Not really (if we get the same copy of http://msdn2.microsoft.com/en-us/library/aa337083.aspx).

The page talks about configuring security for MsDtsServer application, but on Windows 2003 Server and 64-bit XP machine there is another global per-machine setting: in DCOMCNFG right click My Computer, select Properties, find COM Security page and inspect both Edit Limits settings: they should allow the user to access the machine. The simplest way to do it is to add user to Distributed COM Users user group.

|||

I believe I got it to work...

One the webpage http://msdn2.microsoft.com/en-us/library/aa337083.aspx, under "To configure rights for remote users on Windows Server 2003"...

replace step 9 with "Click OK to close the dialog box."

Add a step 9.1 with the following text: "On the same Security tab, under Access Permissions, select Customize, then click Edit to open the Access Permission dialog box."

Add a step 9.2 with the following text: "In the Access Permission dialog box, add or delete users, and assign the appropriate permissions to the appropriate users and groups. The available permissions are Local Access, and Remote Access. The easiest is to add the local DCOM Distributed Users group. "

Add a step 9.3 with the following text: "Click OK to close the dialog box. Close the MMC snap-in."

Step 10 stays as-is: "Restart the Integration Services service."

|||

Thanks alot.

Finally I can connect to SSIS.

Remote SSIS vs Domain\User: Access is Denied (0x80070005)

What OS permissions do I need to give a domain user to effectively connect to a remote instance of Integration Services?

I keep getting the following message:

Cannot connect to SQLDEV01
Failed to retreive data for this request.
Access is denied. (Exception from HRESULT: 0x80070005 (E_ACCESSDENIED)) (Microsoft.SqlServer.ManagedDTS)

I have already performed the Windows 2003 steps outlined in the "Eliminating the Access is Denied" error located at http://msdn2.microsoft.com/en-us/library/aa337083.aspx

I have no problem if the user is a local Administrator (go figure).

The MSDN page lacks another steps needed on W2K3 (not sure about XP) - add the account to Distributed COM Users group. (The page is being updated).|||

Yes, I have done this, and gave the Domain\User the DCOM permissions upon the MsDtsSvr object. The Distributed COM Users step is actually included on ths webpage.

Thanks for responding. Perhaps there is another step missing?

|||

JFoushee wrote:

The Distributed COM Users step is actually included on ths webpage.

Not really (if we get the same copy of http://msdn2.microsoft.com/en-us/library/aa337083.aspx).

The page talks about configuring security for MsDtsServer application, but on Windows 2003 Server and 64-bit XP machine there is another global per-machine setting: in DCOMCNFG right click My Computer, select Properties, find COM Security page and inspect both Edit Limits settings: they should allow the user to access the machine. The simplest way to do it is to add user to Distributed COM Users user group.

|||

I believe I got it to work...

One the webpage http://msdn2.microsoft.com/en-us/library/aa337083.aspx, under "To configure rights for remote users on Windows Server 2003"...

replace step 9 with "Click OK to close the dialog box."

Add a step 9.1 with the following text: "On the same Security tab, under Access Permissions, select Customize, then click Edit to open the Access Permission dialog box."

Add a step 9.2 with the following text: "In the Access Permission dialog box, add or delete users, and assign the appropriate permissions to the appropriate users and groups. The available permissions are Local Access, and Remote Access. The easiest is to add the local DCOM Distributed Users group. "

Add a step 9.3 with the following text: "Click OK to close the dialog box. Close the MMC snap-in."

Step 10 stays as-is: "Restart the Integration Services service."

|||

Thanks a lot.

Finally I can connect to SSIS.

sql

remote sql query

we are trying to execute a remote sql query from one sql server against
another. Both servers are in the same domain. The query is a simple select
* from tablename where surname = whatever. We precede this with an
'opendatasource' statement. The query returns only a few rows. When we run
the query between two sql servers on the same lan segment the query runs in a
second or two. When we run the query between servers on two different
network segments (seperated by a firewall) but still both in the same domain
the same query takes 50+ seconds to run. Looking further, it seems that the
query is actually being executed on the local server so the entire table
(over 1 million rows) is copied locally before running the query. How can I
run the query as a remote query so that the entire query is run on the remote
sql server and only the query results are passed accross the network to the
local server? I have also tried creating a linked server and using OPENQUERY
but still takes ages to run.
Thanks
Open query should pass the query to the other server for execution. Are you
sure you are not doing a JOIN to a local table? In ay case you can create a
stored procedure on the remote server and execute that.
Andrew J. Kelly SQL MVP
"lightningtechie" <lightningtechie@.discussions.microsoft.com> wrote in
message news:4EB250D4-A5D1-4C5C-AC6D-DFA30B09F930@.microsoft.com...
> we are trying to execute a remote sql query from one sql server against
> another. Both servers are in the same domain. The query is a simple
> select
> * from tablename where surname = whatever. We precede this with an
> 'opendatasource' statement. The query returns only a few rows. When we
> run
> the query between two sql servers on the same lan segment the query runs
> in a
> second or two. When we run the query between servers on two different
> network segments (seperated by a firewall) but still both in the same
> domain
> the same query takes 50+ seconds to run. Looking further, it seems that
> the
> query is actually being executed on the local server so the entire table
> (over 1 million rows) is copied locally before running the query. How can
> I
> run the query as a remote query so that the entire query is run on the
> remote
> sql server and only the query results are passed accross the network to
> the
> local server? I have also tried creating a linked server and using
> OPENQUERY
> but still takes ages to run.
> Thanks
|||Perhaps it's the location of the WHERE clause? If you say
SELECT LastName
FROM
OPENQUERY(Otherserver, 'SELECT EmployeeID, LastName FROM
Northwind.dbo.Employees')
WHERE EmployeeID > 5
You are asking for all the rows and then you will apply the where clause
locally.
If you saySELECT LastName
FROM
OPENQUERY(Otherserver, 'SELECT EmployeeID, LastName FROM
Northwind.dbo.Employees WHERE EmployeeID > 5')
You are sending the where clause to the remote server - a good thing, and
only getting the qualified rows returned to the local server.
Rick Byham
MCDBA, MCSE, MCSA
Documentation Manager,
Microsoft, SQL Server Books Online
This posting is provided "as is" with
no warranties, and confers no rights.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:OB7ad5%23KGHA.2604@.TK2MSFTNGP09.phx.gbl...
> Open query should pass the query to the other server for execution. Are
> you sure you are not doing a JOIN to a local table? In ay case you can
> create a stored procedure on the remote server and execute that.
>
> --
> Andrew J. Kelly SQL MVP
>
> "lightningtechie" <lightningtechie@.discussions.microsoft.com> wrote in
> message news:4EB250D4-A5D1-4C5C-AC6D-DFA30B09F930@.microsoft.com...
>

remote sql query

we are trying to execute a remote sql query from one sql server against
another. Both servers are in the same domain. The query is a simple select
* from tablename where surname = whatever. We precede this with an
'opendatasource' statement. The query returns only a few rows. When we run
the query between two sql servers on the same lan segment the query runs in
a
second or two. When we run the query between servers on two different
network segments (seperated by a firewall) but still both in the same domain
the same query takes 50+ seconds to run. Looking further, it seems that the
query is actually being executed on the local server so the entire table
(over 1 million rows) is copied locally before running the query. How can I
run the query as a remote query so that the entire query is run on the remot
e
sql server and only the query results are passed accross the network to the
local server? I have also tried creating a linked server and using OPENQUER
Y
but still takes ages to run.
ThanksOpen query should pass the query to the other server for execution. Are you
sure you are not doing a JOIN to a local table? In ay case you can create a
stored procedure on the remote server and execute that.
Andrew J. Kelly SQL MVP
"lightningtechie" <lightningtechie@.discussions.microsoft.com> wrote in
message news:4EB250D4-A5D1-4C5C-AC6D-DFA30B09F930@.microsoft.com...
> we are trying to execute a remote sql query from one sql server against
> another. Both servers are in the same domain. The query is a simple
> select
> * from tablename where surname = whatever. We precede this with an
> 'opendatasource' statement. The query returns only a few rows. When we
> run
> the query between two sql servers on the same lan segment the query runs
> in a
> second or two. When we run the query between servers on two different
> network segments (seperated by a firewall) but still both in the same
> domain
> the same query takes 50+ seconds to run. Looking further, it seems that
> the
> query is actually being executed on the local server so the entire table
> (over 1 million rows) is copied locally before running the query. How can
> I
> run the query as a remote query so that the entire query is run on the
> remote
> sql server and only the query results are passed accross the network to
> the
> local server? I have also tried creating a linked server and using
> OPENQUERY
> but still takes ages to run.
> Thanks|||Perhaps it's the location of the WHERE clause? If you say
SELECT LastName
FROM
OPENQUERY(Otherserver, 'SELECT EmployeeID, LastName FROM
Northwind.dbo.Employees')
WHERE EmployeeID > 5
You are asking for all the rows and then you will apply the where clause
locally.
If you saySELECT LastName
FROM
OPENQUERY(Otherserver, 'SELECT EmployeeID, LastName FROM
Northwind.dbo.Employees WHERE EmployeeID > 5')
You are sending the where clause to the remote server - a good thing, and
only getting the qualified rows returned to the local server.
--
Rick Byham
MCDBA, MCSE, MCSA
Documentation Manager,
Microsoft, SQL Server Books Online
This posting is provided "as is" with
no warranties, and confers no rights.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:OB7ad5%23KGHA.2604@.TK2MSFTNGP09.phx.gbl...
> Open query should pass the query to the other server for execution. Are
> you sure you are not doing a JOIN to a local table? In ay case you can
> create a stored procedure on the remote server and execute that.
>
> --
> Andrew J. Kelly SQL MVP
>
> "lightningtechie" <lightningtechie@.discussions.microsoft.com> wrote in
> message news:4EB250D4-A5D1-4C5C-AC6D-DFA30B09F930@.microsoft.com...
>

remote perfmon

Hi,
How do i use the performance monitor of my pc to remote performance a
server which is not available in a domain? The server i try to monitor
also has sql server installed on it and i want to log the counters for it.
Is this possible?I haven't tried this but I think all you need to do is establish security
credentials that are recognized by the remote computer before connecting
with perfmon. The easiest way to do this is to map a drive to the remote
machine using the remote machine's local administrator account.
Hope this helps.
Dan Guzman
SQL Server MVP
"Jason" <jasonlewis@.hotmail.com> wrote in message
news:uyo2$ffPGHA.3164@.TK2MSFTNGP11.phx.gbl...
> Hi,
> How do i use the performance monitor of my pc to remote performance a
> server which is not available in a domain? The server i try to monitor
> also has sql server installed on it and i want to log the counters for it.
> Is this possible?

remote perfmon

Hi,
How do i use the performance monitor of my pc to remote performance a
server which is not available in a domain? The server i try to monitor
also has sql server installed on it and i want to log the counters for it.
Is this possible?I haven't tried this but I think all you need to do is establish security
credentials that are recognized by the remote computer before connecting
with perfmon. The easiest way to do this is to map a drive to the remote
machine using the remote machine's local administrator account.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Jason" <jasonlewis@.hotmail.com> wrote in message
news:uyo2$ffPGHA.3164@.TK2MSFTNGP11.phx.gbl...
> Hi,
> How do i use the performance monitor of my pc to remote performance a
> server which is not available in a domain? The server i try to monitor
> also has sql server installed on it and i want to log the counters for it.
> Is this possible?

remote perfmon

Hi,
How do i use the performance monitor of my pc to remote performance a
server which is not available in a domain? The server i try to monitor
also has sql server installed on it and i want to log the counters for it.
Is this possible?
I haven't tried this but I think all you need to do is establish security
credentials that are recognized by the remote computer before connecting
with perfmon. The easiest way to do this is to map a drive to the remote
machine using the remote machine's local administrator account.
Hope this helps.
Dan Guzman
SQL Server MVP
"Jason" <jasonlewis@.hotmail.com> wrote in message
news:uyo2$ffPGHA.3164@.TK2MSFTNGP11.phx.gbl...
> Hi,
> How do i use the performance monitor of my pc to remote performance a
> server which is not available in a domain? The server i try to monitor
> also has sql server installed on it and i want to log the counters for it.
> Is this possible?

remote Local user account permissions to SQL 2000 SP4 server

Is it possible to add a remote / non domain account from another win2003
server to SQL2000 sp4 database'
Thanks,
CactusNo.
For that you would need to create a SQL Server Login or Join the computers
to a domain.
/*
Warren Brunk - MCITP - SQL 2005, MCDBA
www.techintsolutions.com
*/
"cactus" <cactus@.cactus.com> wrote in message
news:Onxc$xl5GHA.4892@.TK2MSFTNGP04.phx.gbl...
> Is it possible to add a remote / non domain account from another win2003
> server to SQL2000 sp4 database'
> Thanks,
> Cactus
>