Regex for finding and replacing SQL Server username and password in connection strings

Question

We just changed our MS SQL Database passwords for 2 ID's. I have over 50 asp.net web applications running off the database which all need the connection strings updated. Is there a handy regex I could use to seach for the server ID, username and password?

Its a long story but I need to be able to match those 3 elements and the connection strings arent all in the same format (some have password before username and vice versa).

This is an example of one of our connection strings:

Data Source=dbserver;Initial Catalog=HOL;Persist Security Info=True;User ID=username;Password=password

I've tried this from regular-expressions but I really am a novice with regex.

\b(?:password\W+(?:\w+\W+){1,6}?user|user\W+(?:\w+\W+){1,6}?password)\b

Edit: I have a find and replace tool (fnr.exe) that I can use, I just need a regex for finding the strings...

Do you have to care about synonyms as well (UID, PWD, Server, Address, Addr, Network Address)? — Damien_The_Unbeliever
– Damien_The_Unbeliever, Commented Jun 24, 2015 at 9:36
can you provide more connection string examples so that we are able to improve our answers? — bro
– bro, Commented Jun 24, 2015 at 9:37
Apologies, yes they all have the same synonyms (User ID, Password and Data Source) — Kieran Quinn
– Kieran Quinn, Commented Jun 24, 2015 at 10:02

bro · Accepted Answer · 2015-06-24 09:32:24Z

1

Here are separate regex for matching

Data Source

\bData\hSource=\w+\b
User ID

\bUser\hID=\w+\b
Password

\bPassword=\w+\b

You can combine them as follows (similar to the example you provided):

\b(?:(User\hID=\w+);(Password=\w+)|(Password=\w+);(User\hID=\w+))\b

DEMO: https://regex101.com/r/bT3yI1/1

answered Jun 24, 2015 at 9:32

bro

7713 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Adam Frederick Wiseman · Accepted Answer · 2015-06-24 09:33:45Z

0

The following Regex should help:

/;?(Data Source|User ID|Password)=((?:&(?:[a-z]+|#[0-9]+);|[^;]){0,})/gi

As a bonus this will also catch HTML entities, e.g. &.

Tested in: https://regex101.com/r/vE2tT0/2

answered Jun 24, 2015 at 9:33

Adam Frederick Wiseman

2231 silver badge6 bronze badges

Comments

user2911332 · Accepted Answer · 2015-06-24 09:35:16Z

0

If you just have to do it once, I'd recommend just running consecutive search and replaces for data source (\bData\hSource=\w+\b) user id (\bUser\hID=\w+\b) and password (\bPassword=\w+\b).

To write one regex that matches all possible combinations is way more effort.

answered Jun 24, 2015 at 9:35

user2911332

1 Comment

Kieran Quinn Over a year ago

There are different combinations, for example server1 with userID1 and password1. Then another could be server1 with userID2 and password2. Doing a generic find and replace is what I have right now and is taking too long

Collectives™ on Stack Overflow

Regex for finding and replacing SQL Server username and password in connection strings

3 Answers 3

Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related