Post reply

logins on all servers?

  • 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!

  • May 23, 2007 at 9:49 am

    #708370

    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

  • May 23, 2007 at 11:31 am

    #708390

    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.

  • May 24, 2007 at 3:20 am

    #708567

    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...

  • May 24, 2007 at 8:06 am

    #708659

    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.

  • May 24, 2007 at 11:07 am

    #708727

    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.

  • May 25, 2007 at 12:50 pm

    #709071

    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.

  • May 25, 2007 at 1:40 pm

    #709088

    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.

  • May 25, 2007 at 2:12 pm

    #709092

    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.

  • May 30, 2007 at 11:20 am

    #709771

    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.

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

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