logins on all servers?

Question

logins on all servers?

jack-64275

SSCrazy

Points: 2017
More actions
May 23, 2007 at 8:42 am

#196041

We currently have well over 250 sql servers in house and I need to be able to periodically query to determine if there are any logins that do not have passwords.
Any ideas of how I could automate this process to gather this data from all servers in one "job" and output to something like a spreadsheet?

Thanks!

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

Set up all your servers as linked servers on a master server. Then loop through each server with a query that inserts all logins with a null password into a temp table. Last, you can bcp from the temp table into a spreadsheet or CSV file or whatever you like.

John

jack-64275 SSCrazy Points: 2017 More actions · Answer 2

Thanks. Probably not possible but I was hoping to avoid te overhead of setting up all those linked servers and having to add every time a new server comes on line.

Moreno-84941 Hall of Fame Points: 3258 More actions · Answer 3

Just an idea to avoid the linked server . Write a store procedure, set up a scheduled job and store the result on a share ore send it by mail.....I know, I know it is a lot of manual work...

Paul Rubel Ten Centuries Points: 1047 More actions · Answer 4

The Base line Security Analyser does a good job of flagging passwords that are not strong. And I beleive it can be automated to run.

barsuk SSChampion Points: 12280 More actions · Answer 5

Every time you install new server you can run custom script which will create a linked server on a fly. make sure you choose strong password for that.

Omri Bahat-433962 SSC Eights! Points: 901 More actions · Answer 6

I work for a SQL tools vendor, so I will break up my reply into two:

1. In case you are considering 3rd party tools, the company I work for has a tool with which you can query all 250 servers in parallel and get a single result set in response to your queries, so that in one click you get all this info and can auto-save the results to an output (e.g., monitoring) database.

2. If you are not considering 3rd party tools, then there are 3 ways to achieve cross- server communications:

- linked servers (covered above)

- OpenRowSet commands - do not require you to set up linked servers and let you query one database on another server at a time.

- osql - command line utility that can be called to connect from your local machine (for example) to remote databases and servers.

All 3 methods are serial (i.e., one call at a time).

If you choose to go with any of the last 3, there are many examples on this (and other) sites that show how to use them.

Hope this helps.

jack-64275 SSCrazy Points: 2017 More actions · Answer 7

The tool sounds like the easiest way to go but I work for a bank so getting $$ is pretty tough

In the OPENROWSET would it be possible to do something like

INSERT INTO OPENROWSET (connection info SELECT * FROM MY_AUDIT_TABLE) SELECT name from sysxlogins where password IS NULL

and have that as a scheduled job on all servers? Then just report off MY_AUDIT_TABLE based on managements frequency requirements?

I know, I know... why don't I just try it and find out right? I will play around with it but it's Friday before Memorial Day and like the sign at home says

"It's five o'clock somewhere"

Thanks for all the suggestions.

Omri Bahat-433962 SSC Eights! Points: 901 More actions · Answer 8

The syntax needs a little work, but yes- you can do something similar (see important comments below about security!)

INSERT INTO

Example:

INSERT INTO ...

SELECT * FROM OPENROWSET

HOWEVER!!!

Since you work for a bank, you will need to be a little careful about security- to use these commands you need to provide your credentials to make the remote connection (which is the main reason why people do not use this). In other words, you're probably subject to SOX or other security regulations that may be violated by using this technique.

Scott Coleman One Orange Chip Points: 27452 More actions · Answer 9

You might step out of the T-SQL world for a minute and consider doing something with SMO or DMO in Visual Basic, C#, or your language of choice. There are methods to enumerate all the visible servers on the network, assuming you're talking about servers in the same domain. Given the server names it is trivial to connect to each one in turn and run a query. If you can't see every server or connect with Windows authentication, you could create a table with all the server names and connection details. This could be a SQL Server table, a file, or any other data source with whatever level of protection or encryption you desire.

You could use the same table idea to generate the OPENDATASOURCE queries with dynamic SQL, but using VB or another language to retrieve connection info gives you more options for securing passwords. Either way, a table of server names would be much easier to maintain than 250 linked server definitions.