Need to redirect .net app to another sql server

Question

Need to redirect .net app to another sql server

Jon Higgins

Valued Member

Points: 55
More actions
September 27, 2013 at 8:56 am

#395572

Hi,
I'm dealing with a situation where one of our developers is compiling a component with the connection string to a SQL Server instance included in the compile. Is there a way to redirect the connection from the compiled connection to another SQL Server instance. Connection parameters including database and security remain the same. Basically we're looking for a situation where the compiled component can retain the same connection information, but have it actually connect to ServerB rather than ServerA.

Viewing 15 posts - 1 through 15 (of 16 total)

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

Sean Lange SSC Guru Points: 286590 More actions · Answer 1

Jon Higgins (9/27/2013)
Hi,
I'm dealing with a situation where one of our developers is compiling a component with the connection string to a SQL Server instance included in the compile. Is there a way to redirect the connection from the compiled connection to another SQL Server instance. Connection parameters including database and security remain the same. Basically we're looking for a situation where the compiled component can retain the same connection information, but have it actually connect to ServerB rather than ServerA.

Why would you compile a connection string into the application? That is what config files are for.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Jon Higgins Valued Member Points: 55 More actions · Answer 2

I've been asking the same question, but I'm not the developer. I'm looking for a kludge until we can hash this out.

Sean Lange SSC Guru Points: 286590 More actions · Answer 3

Jon Higgins (9/27/2013)
I've been asking the same question, but I'm not the developer. I'm looking for a kludge until we can hash this out.

IF the server name is using the name and not IP maybe you can add an entry in the hosts file on your app box? Of course, that means that ALL connections from any app will use the "alternate" server.

Meanwhile, walk up to that developer and punch him straight between the eyes. It is unacceptable to hard code a connection string into an application. There are so many reasons that is a bad idea I could spend the rest of the day typing them in. I am guessing of course I am preaching to the choir.

Can't you go to that developer and have them fix their code?

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Kurt W. Zimmerman SSCrazy Eights Points: 8676 More actions · Answer 4

Sean Lange (9/27/2013)
Can't you go to that developer and have them fix their code?

+1

Kurt W. Zimmerman
SR DBA
Lefrak Organization
New York, NY

http://www.linkedin.com/in/kurtwzimmerman

Jon Higgins Valued Member Points: 55 More actions · Answer 5

I tried the hosts file approach early on and it didn't seem to work. I also tried the Client Configuration utility to see if that works. Apparently when the app compiles it's actually storing the connection string with the IP address for the SQL Server rather the named instance. It seems to be getting around anything I try to put in its way.

The component is being rewritten as we speak, but I'm looking for a little security if we have to switch servers.

Sean Lange SSC Guru Points: 286590 More actions · Answer 6

Jon Higgins (9/27/2013)
I tried the hosts file approach early on and it didn't seem to work. I also tried the Client Configuration utility to see if that works. Apparently when the app compiles it's actually storing the connection string with the IP address for the SQL Server rather the named instance. It seems to be getting around anything I try to put in its way.
The component is being rewritten as we speak, but I'm looking for a little security if we have to switch servers.

The security blanket you desire here is done by putting connection information outside of the code. Have this developer use a config file instead of hardcoding connection information into the app.

If they put in the IP there is little you can do, short of some DNA rerouting internally but that is putting a lot of effort into dealing with the symptom instead of fixing the issue.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Ed Wagner SSC Guru Points: 287026 More actions · Answer 7

Sean Lange (9/27/2013)

Jon Higgins (9/27/2013)
Meanwhile, walk up to that developer and punch him straight between the eyes. It is unacceptable to hard code a connection string into an application.

+1

Fixing the problem (the developer) is the way I would go. Band-aiding it will only cause headaches later.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

Kurt W. Zimmerman SSCrazy Eights Points: 8676 More actions · Answer 8

Ed Wagner (9/27/2013)

Sean Lange (9/27/2013)
Jon Higgins (9/27/2013)
Meanwhile, walk up to that developer and punch him straight between the eyes. It is unacceptable to hard code a connection string into an application.
+1
Fixing the problem (the developer) is the way I would go. Band-aiding it will only cause headaches later.

Get right down to it this is not a DBA issue but a developer issue. I like 2x4's myself! 😉 😀

Kurt

Kurt W. Zimmerman
SR DBA
Lefrak Organization
New York, NY

http://www.linkedin.com/in/kurtwzimmerman

Jon Higgins Valued Member Points: 55 More actions · Answer 9

I'm getting some blow-back about "Microsoft .Net Best Practices". Any response I could give there?

Sean Lange SSC Guru Points: 286590 More actions · Answer 10

Jon Higgins (9/27/2013)
I'm getting some blow-back about "Microsoft .Net Best Practices". Any response I could give there?

Is this dev actually trying to say that MS says it is best practice to hard code connection strings inside the app???

Ask him what year he read this. Maybe in the late 80s but developers have been putting stuff like in external files as best practice for at LEAST 20 years!!! VB/C/C++ etc all used ini files. Then in 2003 along came .NET and since the beginning it has been best practice to use app/web config files.

I have been a .NET developer since 2003 and I have NEVER EVER EVER hardcoded a connection string in an application. Now there are some legitimate security concerns about putting that in the config in plain text. That is easily rectified with some low level encryption.

Where is the "best practices" advice coming from? That has to be one of the most ridiculous things I have heard in the last month.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

WilburBud SSCrazy Points: 2914 More actions · Answer 11

Jon Higgins (9/27/2013)
I'm getting some blow-back about "Microsoft .Net Best Practices". Any response I could give there?

Did they actually SHOW you a reference? And beyond the developer, what do the designers/architects say? Any design specification document?

Kurt W. Zimmerman SSCrazy Eights Points: 8676 More actions · Answer 12

I have to agree with Sean on this. It does not make any sense to hard-code database connections. It presents so much work for the developer and produces a real pain for those who manage the application.

I've worked on many .NET solutions and I don't ever recall a single time where connections were done with hard coded credentials.

My previous position we had to take a production system to a DEV & UAT environment. All SQL Server, web server, file server information was located outside the code base. This allow the flexibility to move the code base around from DEV, UAT and then finally PROD.

Doesn't make any sense...

and here I thought DBAs were lazy... Get a bigger 2x4!

Kurt

Kurt W. Zimmerman
SR DBA
Lefrak Organization
New York, NY

http://www.linkedin.com/in/kurtwzimmerman

Jon Higgins Valued Member Points: 55 More actions · Answer 13

Forget that last thing. I misinterpreted the best practices thing.

Sean Lange SSC Guru Points: 286590 More actions · Answer 14

Hard coded connection strings are for this guy.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/