How to make SMO use TCP instead of NP for server connections?

We have ~30 servers that don't have Named Pipes enabled and I can't stop/start the services until the next Maintenance Window (30 days away). I need to query all the server for users and logins (Security Audit). I want to use SMO since working with its collections will save me a lot of coding. It seems that the default protocol for SMO is Named Pipes so I get connection failure for those servers that don't have NP enabled. Is there any way to have SMO use TCP instead of NP for connecting?

SMO uses the ConnectionManager class to create connections, this class assembles the connection string based on the passed variables. The connection will pick the protocol order the client has specified to connect to the server. If you want to force the connection to use Named Pipes, you might use:

Instead of just the servername use npervername

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||Thanks - I'll give a try, but I need to TCP so I think it would tcpervername.

|||

Yep, sure you are right, you should use (without smileys :-) )

Code Snippet

tcp:Servername

|||

It didn't work. I probably haven't set the variables up correctly. I found the ConnectionContext last night and plugged it in this morning.That works but I'd like to see how your syntax should be put together if you don't mind.

note - the forum software put the Smileys in. I typed a colon as I'm sure you did (this one is intentional)

Try

svrname = Trim(svrname)

svrname = "tcpERVERNAME"

Dim svr As Server = New Server(svrname)

'svr.ConnectionContext.NetworkProtocol = NetworkProtocol.TcpIp

For Each db In svr.Databases

If db.Status = 1 Then

....

...

|||Mea Culpa - It seems there was a typo in the server name. It does work. Thank you for your help.

How to lock out everyone but me?

I'm working with SQL Server Express, and I want to configure a named instance so that only the 'sa' user and a specified SQL Server user with a specified password have access. In particular, I'm trying to lock out BUILTIN\Administrators. Furthermore, I need to be able to do this from a command-line, since I want to configure it in a script. Nothing I do seems to work.

I've attempted to use sqlcmd and the T-SQL call ALTER LOGIN [BUILTIN\Administrators] DISABLE, but that returns the error "Cannot alter the login 'BUILTIN\Administrators' because it does not exist or you do not have permission."

What I can (apparently) successfully do is run DENY CONTROL TO [BUILTIN\Administrators]. This runs without reporting an error. However, after running it against the 'master' database and the specific database in my named instance I care about, I can still run the following:

sqlcmd -S (local)\MyInstance -d MyDB -Q "select * from my_table"

and see the contents of my_table.

What do I need to do to restrict access exclusively to 'sa' and other SQL users I designate?

Have you tried simply dropping the builtin\administrators login?

"Drop login [builtin\administrators]"

You can check sys.server_principals for a list of all logins. This seemed to work for me, although I tested this on Developer Edition and not Express Edition.

Sung

|||

I think the right expression would be to deny connection to builtin administrator under normal circumstances.

I want to clarify that actually stopping a box administrator from accessing SQL Server instance (and any other service for that matter) is practically impossible as your adversary has control over the OS and most likely the hardware.

You need to trust the box admin to some extent, and a proper channel and policies to monitor unauthorized activities (i.e. company policies, box auditing, etc.) should be in place to keep a box administrator honest. If you are hosting any data on a machine where you don’t trust the box administrator, make sure you don’t store (and I would even say don’t manipulate) any valuable information in clear text on the system you don’t trust.

If your objective is to keep an honest box administrator honest and deter her from accessing SQL Server, revoking access as Sung mentioned should be enough. In SQL Express you additionally would have to drop the [Builtin\users] account. SQL Express is designed OOB to grant access to the instance (with low privileges) to any user in the system in order to take advantage of some SQL Express-specific features.

BTW. Keep in mind that in SQL Server 2005 it is sufficient to start the server in single user mode. The reason for this is to allow a box admin to recover the system in case of emergency.

I hope this information will be useful.

-Raul Garcia

SDE/T

SQL Server Engine