| Author |
Topic |
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
 Posted - 2006-01-13 : 03:31:31
|
| Which is better...creating a dsn entry for each appliication that will connect from a webserver to sql server ordefining the connection information via the application using sqloledb or some other drivers?TIA--------------------keeping it simple... |
|
|
Westley
Posting Yak Master
229 Posts |
Posted - 2006-01-13 : 03:48:44
|
| Hi jen, As far as I remember, dns-less is faster, if performance is an issue. you might need to encrypt you login and password in the file tho.I never use DNS entry for any of my applications, found it easier to work with dns-less :) |
 |
|
|
mr_mist
Grunnio
1870 Posts |
Posted - 2006-01-13 : 04:25:34
|
| Yep, DSN-Less oledb is best.-------Moo. :) |
|
|
|
afrika
Master Smack Fu Yak Hacker
2706 Posts |
Posted - 2006-01-13 : 06:09:01
|
| http://www.stardeveloper.com/articles/display.html?article=2000120101&page=1http://www.powerasp.com/content/database/dsn_vs_dnsless.asp |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-01-13 : 06:22:56
|
| We use DNS-less, in a separate INCLUDE file which is NOT below the WWW root - so its not available to a browser.Do NOT name the file extension ".INC" as (in the event that it IS available to IIS) that will be rendered as Text - whereas calling it .ASP [for example] will cause IIS to "render" it, and not show the source.Use trusted connection, rather that User+Password - thus anyone who hacks the source code still won't be able to connect.Set up a non-standard Port number for the SQL Server - to prevent port scanners from "finding" the SQL service, and overloading it with login hack attempts.Probably some other stuff too that hasn't leapt to the front of my aging brain!Kristen |
|
|
|
Rovastar
Starting Member
38 Posts |
Posted - 2006-01-13 : 07:10:51
|
| Ummh interesting.....In the environment I am in at the moment we have a mixture. From an admin perspective (which I am, not a dev) I think it is easier to give the devs a DSN. That way we – the admins - have control over it.It a database moves servers, etc or whatever reason we can change the database information and it does not involved contacting the 50 or so different developers in different countries for them to make the changes to the inc for each site.From a dev point of view it is easier and ‘better’ for them to use a DSN-less system but in a structured web environment with dev design the website and admin look after the db and servers the ‘best’ IMHO setup is to have DSNs.As to the overhead the probably would be more overhead with separate DSNs but I am unclear by how much.For using DSN-less connections Kristen’s comments should be followed. Never use plain text usernames and password in a file used the trusted connections. One day no doubt an exploit or hack will come along and all the password will be exposed. |
|
|
|
Rovastar
Starting Member
38 Posts |
Posted - 2006-01-13 : 07:19:29
|
| Also from the links which are written from a dev point of view:"Why to use DSN Connections ? * Provides easy to remember data source names. * When there are lots of data sources to think of and you want a central repository to hold the collection of data sources without having to worry about the actual site and configuration of the data sources. Why to use DSN less Connections ? * When you can't register DSNs yourself e.g. when you are running a virtual hosting account on other's server. Stop emailing system administerator, connect to your databases directly. * Provides faster database access because it uses native OLE DB providers, while DSN connections make use of ODBC drivers. My ExperienceI always use DSN less connections on my site and examples :)."Typical dev attitude “Stop emailing system administerator, connect to your databases directly.“ Circumnavigate the admin procedures and do it yourself. *sigh* |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-01-13 : 08:33:21
|
| "it does not involved contacting the 50 or so different developers in different countries"I'm in the lucky (??!) position that the structure of the Include file is prescribed. All our web sites have the same file, in the same relative location, with the same "name" for the DSN. Thus any Tom, Dick or Harry can move a web site to a different server - provided they are a certified, bona fida, admin, of course!!Kristen |
|
|
|
mcrowley
Aged Yak Warrior
771 Posts |
Posted - 2006-01-13 : 10:11:54
|
| We are moving from DSNs to DNS aliases. This way, you can use OLEDB/SQLProvider, and if we need to move a machine, we don't have to hunt down 100 webconfig files. |
|
|
|
CorpDirect
Yak Posting Veteran
92 Posts |
Posted - 2006-01-13 : 17:15:17
|
Great suggestions for a fledgling admin/developer. Can anyone offer an example of how to connect DSN-less with a trusted connection, as recommended by Kristen?Most useful to me would be something relative to our environment, which is MSSQL 2000, PHP under Apache on Windows, non-standard port (e.g. 51433). In development we have been connecting using syntax recommended from PHP forums, with an include like db_connect.php containing e.g.:<?php$dbserver = "servername";$dbuserid = "username";$dbpasswd = "userpass";$dbname = "database";$dbconn = MSSQL_CONNECT($dbserver,$dbuserid,$dbpasswd) or DIE("DATABASE FAILED TO RESPOND.");mssql_select_db($dbname) or DIE("Database unavailable");?>According to your suggestions above this is not the most secure method, as the connection and authentication data is stored in the file. How could we accomplish this in a more secure manner?Thanks,Daniel |
|
|
|
afrika
Master Smack Fu Yak Hacker
2706 Posts |
Posted - 2006-01-13 : 18:21:09
|
| http://www.connectionstrings.com/ |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-01-16 : 05:38:35
|
| Have a look at the link afrika posted. Very useful resource that one. The one you want it probably:SQL Server : OleDB : Trusted connectionIt has "SSPI" in it somewhere IIRC!!Kristen |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2006-01-16 : 13:28:35
|
quote:
Originally posted by Rovastar
Ummh interesting.....In the environment I am in at the moment we have a mixture. From an admin perspective (which I am, not a dev) I think it is easier to give the devs a DSN. That way we – the admins - have control over it.It a database moves servers, etc or whatever reason we can change the database information and it does not involved contacting the 50 or so different developers in different countries for them to make the changes to the inc for each site.From a dev point of view it is easier and ‘better’ for them to use a DSN-less system but in a structured web environment with dev design the website and admin look after the db and servers the ‘best’ IMHO setup is to have DSNs.As to the overhead the probably would be more overhead with separate DSNs but I am unclear by how much.For using DSN-less connections Kristen’s comments should be followed. Never use plain text usernames and password in a file used the trusted connections. One day no doubt an exploit or hack will come along and all the password will be exposed.
When moving database servers, you should not have to contact developers to change the DSN-less connection. The connection string should be stored in a file that the admins can get to and change. Developers should not have anything to do with moving servers. If your admins don't have access to these external files, then that problem should be corrected. DSNs just do not need to be used anymore and shouldn't either.Tara Kizeraka tduggan |
|
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2006-01-16 : 20:59:03
|
| there are good points raised for each choice...in my previous jobs, we have a mixture of both because:1. third party tools like to use dsn2. internally developed apps are using connection files that are encryptedAnd from experience, files are fairly manageable. If a change is made, we use an application server that will propagate the change across workstations depending on which application is affected. So start-up for workstations gets a bit slower but it's a small price to pay for synchronization. Now I was playing with the idea that dsn can fasten the start-up process.But adding a dsn entry everytime a new connection has to be made and maintaining all of them will be a nightmare? Especially if you have to make major changes like re-allocating databases on different servers in terms of capacity planning.Also, is there some sort of performance hit if there are n-number of entries or how many entries can be made?--------------------keeping it simple... |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-01-17 : 02:45:42
|
| What you need are web based applications Jen. One web application, one config file. Client just needs a URL. Job done!Kristen |
|
|
|
CorpDirect
Yak Posting Veteran
92 Posts |
Posted - 2006-01-18 : 09:16:06
|
quote:
Originally posted by KristenSQL Server : OleDB : Trusted connectionIt has "SSPI" in it somewhere IIRC!!
I'll have to learn about how and where to use this, as it's new to me. But this sounds like the right tack to follow.Thanks afrika and Kristen!Daniel |
|
|
|
CorpDirect
Yak Posting Veteran
92 Posts |
Posted - 2006-01-19 : 17:20:55
|
| OK, I'm stuck. Every reference I can find on the 'net about configuring a trusted connection seems to reference IIS and/or ASP.NET. We're running Apache with PHP.I'd love to be able to use a trusted connection, but I can't find anything, anywhere, that shows me how to create one! Trusted connections are all new to me, so I apologize if I'm just not getting it..but can anyone help?Thanks for any direction anyone can offer!Regards,Daniel |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2006-01-19 : 17:59:30
|
| Trusted connections mean you'd be using Windows Authentication to connect to the database server. Your application isn't on a Windows box, right? Not sure how you'd be able to pass windows credentials when you wouldn't have those credentials on your box.Tara Kizeraka tduggan |
|
|
|
CorpDirect
Yak Posting Veteran
92 Posts |
Posted - 2006-01-24 : 10:07:33
|
| Actually the application runs on a Windows server. This wasn't my decision (or even my recommendation), but may make using a trusted connection possible.The application server is not, however, a member of the domain, while the SQL 2000 server is a domain member. If this is still possible I'd love to learn how!Thanks,Daniel |
|
|
|
|
dsn or dsn-less?