Visual Studio .NET - How to keep sql database in online server and access it by C# windows/web application

Asked By yashalakshmi on 31-May-13 02:28 AM
I have a client with requirements as follows :

  They want windows based project and need to keep sql database in online server. How can I access sql database from online server in C# windows based application or webapplication. What connectionstring can be used to access database? 
Robbe Morris replied to yashalakshmi on 31-May-13 09:34 AM
You can connect to a sql server instance on a remote server.  

 <connectionStrings>
    <add name="ConnectionString" connectionString="Server=server name goes here or ip address;Database=DataBaseNameGoesHere;UID=SQLUserNameGoesHere;pwd=SQLUserPasswordGoesHere;Connection Timeout=120;" providerName="System.Data.SqlClient"/>
  </connectionStrings>

Your main issues is that you really should not expose a sql server instance over the public internet.  You'll have thousands upon thousands of automated bots hammer that network port attempting to hack it.  For your desktop app that needs to access the data over the internet, you'll want to build a web services layer or WCF service that is hosted in IIS.  The web service would then have direct connectivity to your database.
yashalakshmi replied to Robbe Morris on 01-Jun-13 06:46 AM
Thank you sir for your valueable respone. I will follow the points u have mentioned and revert back if any problem. Thank u sir once again.
Bill B replied to yashalakshmi on 18-Jun-13 02:42 AM
for further study: WCF service with a custom config at your desired location
and add your own encryption

overload the config and make your own custom

rather than using the machine.config keys and the encryption
for the same reason your system can decode quickly, so can hacker

just saying...
adjusted from I forgot who, it was not me, sorry I can't give credit, he's out there
maybe   Alexander, Pete or
Proposed As Answer by http://social.msdn.microsoft.com/profile/youssef%20moussaoui%20-%20msft/?type=forum&referrer=http://social.msdn.microsoft.com/Forums/hu-HU/wcf/thread/cae6d32f-50bc-473c-b508-8481f63b1721

Placing <system.serviceModel>..... configuration information in a config file other than app.config




using System;
using System.Collections.Generic;
//using System.Linq;
//using System.Text;
using System.ServiceModel;

///
/// This class exist solely to enable use of config files not named web.config or app.config
/// well, also so you can have the config file location somewhere other than current directory
///
///
namespace WCFConnector
{
    public class WCFwNamedConfigFile : ServiceHost
    {
    public WCFwNamedConfigFile(string CustomConfigPath, Type serviceType, params Uri[] baseAddresses)
      : base()
    {
      this.CustomConfigPath = CustomConfigPath;
      InitializeDescription(serviceType, new UriSchemeKeyedCollection(baseAddresses));
    }

    public string CustomConfigPath { get; private set; }

    protected override void ApplyConfiguration()
    {
      String sPathNoDoubleBack =  CustomConfigPath.Replace("\\\\","\\");
      if (string.IsNullOrEmpty(CustomConfigPath) )
      base.ApplyConfiguration();
      else
      if (System.IO.File.Exists(CustomConfigPath) == true)
        LoadConfigFromCustomLocation(CustomConfigPath);
      else
        base.ApplyConfiguration();
    }

    private void LoadConfigFromCustomLocation(string configFilename)
    {
      var filemap = new System.Configuration.ExeConfigurationFileMap();

      filemap.ExeConfigFilename = configFilename;

      System.Configuration.Configuration config =
      System.Configuration.ConfigurationManager.OpenMappedExeConfiguration
      (filemap,
     System.Configuration.ConfigurationUserLevel.None);

      var serviceModel = System.ServiceModel.Configuration.ServiceModelSectionGroup.GetSectionGroup(config);

      bool loaded = false;
      foreach (System.ServiceModel.Configuration.ServiceElement se in serviceModel.Services.Services)
      {
      if (!loaded)
        if (se.Name == this.Description.ConfigurationName)
        {
        base.LoadConfigurationSection(se);
        loaded = true;
        break;
        }
      }
      if (!loaded)
      throw new ArgumentException("ServiceElement doesn't exist");
    }
    }
}