Skip to Content
Author's profile photo Hynek Petrak

SAP .NET Connector – storing connection details in SQL Server

Hi,

I’ve seen many examples of IDestinationConfiguration implementations, where the connection details are more or less hard-coded. Find below an example, where a MS SQL Server table is used as storage for connection parameters:


public class SqlDestinationConfiguration : IDestinationConfiguration {
    public RfcConfigParameters GetParameters(string name) {
        RfcConfigParameters cp = new RfcConfigParameters();
        using (SqlConnection con = new SqlConnection(
            ConfigurationManager.ConnectionStrings["MyConnectionString"].ConnectionString)) // as defined in your [app|web].config file
        using (SqlCommand cmd = new SqlCommand()) {
            cmd.CommandText = "SELECT DISTINCT ApplicationServer, Client, " +
                "SystemNumber, Language, " +
                "UserName, encpwd, id, description, MessageServer, " +
                "System, UseMsgServer, GroupName, MsgServerPort " +
                "FROM [SAPSystems] Where [ID] = @id";
            cmd.CommandType = CommandType.Text;
            cmd.Connection = con;
            cmd.Parameters.AddWithValue("id", name);
            cmd.Connection.Open();
            using (SqlDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection)) {
                if (rdr.HasRows) {
                    rdr.Read();
                    string encp = rdr.GetString(5); // encrypted password
                    string ppw = null;
                    try {
                        // password encryption/decryption can be omitted
                        ppw = DecryptPassword(encp, name); // decrypt password with salt = name (custom function)
                    } catch (Exception ex) {
                        throw new Exception("Failed to decrypt the password: " + ex.Message);
                        return null;
                    }
                    // 0-ApplicationServer, 1-Client, 2-SystemNumber, 3-Language,
                    // 4-UserName, 5-encpwd, 6-id, 7-description, 8-MessageServer,
                    // 9-System, 10-UseMsgServer, 11-GroupName, 12 - MsgServerPort
                    if (rdr.GetBoolean(10)) { // load balanced?
                        cp.Add(RfcConfigParameters.MessageServerHost, rdr.GetString(8));
                        cp.Add(RfcConfigParameters.LogonGroup, rdr.GetString(11));
                        string port = rdr.GetString(12);
                        if (!string.IsNullOrWhiteSpace(port)) { // only if defined
                            cp.Add(RfcConfigParameters.MessageServerService, port);
                        }
                    } else {
                        cp.Add(RfcConfigParameters.AppServerHost, rdr.GetString(0));
                        cp.Add(RfcConfigParameters.SystemNumber, rdr.GetString(2));
                    }
                    cp.Add(RfcConfigParameters.Client, rdr.GetString(1));
                    cp.Add(RfcConfigParameters.SystemID, rdr.GetString(9));
                    cp.Add(RfcConfigParameters.User, rdr.GetString(4));
                    cp.Add(RfcConfigParameters.Password, ppw);
                    cp.Add(RfcConfigParameters.Codepage, "1100");
                    cp.Add(RfcConfigParameters.Language, rdr.GetString(3));
                    cp.Add(RfcConfigParameters.PeakConnectionsLimit, "101");
                } else {
                    return null; // no rows returned
                }
            } // end using rdr
        } // end using con and cmd
        return cp;
    }
    public bool ChangeEventsSupported() {
        return false; // can change any time asynchronously
    }
    public event RfcDestinationManager.ConfigurationChangeHandler ConfigurationChanged;
}

Assigned Tags

      1 Comment
      You must be Logged on to comment or reply to a post.
      Author's profile photo Hynek Petrak
      Hynek Petrak
      Blog Post Author

      Added message server port parameter to the code, plus properly 'using' the Sql objects.

      (Somehow the syntax highlighting does not work anymore.)