0

I am writing a small tool to automate some trivial tasks in SQL Server 2008 R2 (if it matters). One of the features it needs to do is read a csv file of users and passwords, and create logins in SQL Server. This works great. Except in the case where one of the passwords does not comply with SQL Server Password Complexity requirements, in which case it breaks miserably.

In order to mitigate this management has decided that we need another tool, to validate the integrity of csv files, to ensure that all supplied passwords conform to the requirement of a supplied SQL Server. However, short of simply trying them all, and noting which failed (and then cleaning up), there doesn't seem to be any elegant way to check whether a given password is valid for a given SQL Server.

Is this a correct assumption? Is is there some simpler way to query the Database about password requirements (or failing that whether a given password is compliant) without making changes to the database?

These tools are being written in C#.

Mikkel Løkke
  • 3,710
  • 23
  • 37
  • 1
    Seems to be a statement of what you are doing - what is your actual question? – PaulF Oct 05 '15 at 08:11
  • Sorry. It's more a statement of what I'm trying to do. I'll re-phrase it more as a question. – Mikkel Løkke Oct 05 '15 at 08:13
  • I guess that it is much easier to catch the exception and log it to a separate text file instead of having another tool. – Stefan Steinegger Oct 05 '15 at 08:25
  • It would be a *lot* simpler if you used Windows Authentication. It would be more secure and the domain would take care of complexity rules, expirations etc. You can even use [managed service accounts or virtual](https://technet.microsoft.com/en-us/library/dd548356(v=ws.10).aspx) to have the OS automatically renew passwords, or even do away with them – Panagiotis Kanavos Oct 05 '15 at 08:33
  • Stefan: Yeah. We've already run all that by management. We also told them that the tool was worthless, because a csv file validated at time A is not necessarily still valid at time B, and about 20 other obvious flaws in their logic. But "this is what the customer wants". I'm not responsible for the quality of the business decisions, just the quality of the technical ones. What I have now sort of works, but isn't super elegant. I was just wondering if there was a better way. – Mikkel Løkke Oct 05 '15 at 08:36
  • Panagiotis: A lot of things would be easier if we could dictate to the customers how they run their business. However I don't think that FedEx is going to make sweeping changes to their IT infrastructure, because it's inconvenient to a small Danish ISV. – Mikkel Løkke Oct 05 '15 at 08:39

1 Answers1

1

This is not an easy problem. SQL Server uses the NetValidatePasswordPolicy function to check the password validity. You can try to use this function on your own, but you must have a few things into account. Please, see thi SO Q&A: Calling NetValidatePasswordPolicy from C# always returns Password Must Change which give some pointers on how to use this function.

However, an easiser solution would be to try to create a dummy user with all the possible passwords, and check for errors. That will let you log a list of user whose passwords doesn't comply the complexity rules.

Community
  • 1
  • 1
JotaBe
  • 38,030
  • 8
  • 98
  • 117