/* This does not work*/
private void ConnectWithCredential()
{
try
{
string connectionString = ConfigurationManager.ConnectionStrings["rulesDB"].ConnectionString;
using (SqlConnection connection = new SqlConnection(connectionString))
{
SecureString password = new SecureString();
char[] chars = pwd.Text.ToCharArray();
for (int x = 0; x < chars.Length; x++)
{
password.InsertAt(x, chars[x]);
password.AppendChar(pwd.Text[x]);
}
password.MakeReadOnly();
SqlCredential credential = new SqlCredential(userId.Text, password);
connection.Open();
connection.ChangeDatabase(dbName.Text);
DisplayConnectionDetails(connection);
}
}
catch (Exception ex)
{
errorDiv.InnerText = ex.ToString();
}
}
private void InvalidCombinationCheck(SqlCredential credential)
{
using (SqlConnection connection = new SqlConnection(_builder.ConnectionString, credential))
{
Assert.Throws <InvalidOperationException>(() => connection.AccessToken = "SampleAccessToken");
}
}
/// <summary>
/// Gets a connection to the database using the specified connection string and credentials.
/// </summary>
/// <param name="connectionString">The connection string.</param>
/// <param name="credentials">The credentials.</param>
/// <returns></returns>
/// <exception cref="ArgumentNullException">Connection string and credentials may not be null.</exception>
public static IDbConnection GetConnection(string connectionString, SqlCredential credentials)
{
Requires.NotNullOrEmpty(connectionString, nameof(connectionString));
Requires.NotNull(credentials, nameof(credentials));
return(new SqlConnection(connectionString, credentials));
}
public void Initialize(SecureString pw)
{
var count = read.StreamReader(input);
var connectionString = ConfigurationManager.ConnectionStrings["astrodatasql"].ConnectionString;
SqlCredential cred = new SqlCredential("astrodatasql", pw);
Console.WriteLine("Attempting to log into {0}", "astrodatasql");
using (SqlConnection conn = new SqlConnection(connectionString, cred))
using (SqlCommand cmd = new SqlCommand(query_insert, conn))
{
Console.WriteLine("Successfuly logged in.");
Int32 rowsAffected = 0;
cmd.Parameters.Add("@Country", System.Data.SqlDbType.NVarChar);
conn.Open();
for (var i = 0; i < count.Count; i++)
{
Console.WriteLine("Adding {0} to the table", count[i].CountryName);
cmd.Parameters["@Country"].Value = count[i].CountryName;
rowsAffected += cmd.ExecuteNonQuery();
}
try
{
Console.WriteLine("RowsAffected: {0}", rowsAffected);
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
}
}
public void ConnectionTestInvalidCredentialCombination()
{
var cleartextCredsConnStr = "User=test;Password=test;";
var sspiConnStr = "Integrated Security=true;";
var testPassword = new SecureString();
testPassword.MakeReadOnly();
var sqlCredential = new SqlCredential(string.Empty, testPassword);
// Verify that SSPI and cleartext username/password are not in the connection string.
Assert.Throws <ArgumentException>(() => { new SqlConnection(cleartextCredsConnStr, sqlCredential); });
Assert.Throws <ArgumentException>(() => { new SqlConnection(sspiConnStr, sqlCredential); });
// Verify that credential may not be set with cleartext username/password or SSPI.
using (var conn = new SqlConnection(cleartextCredsConnStr))
{
Assert.Throws <InvalidOperationException>(() => { conn.Credential = sqlCredential; });
}
using (var conn = new SqlConnection(sspiConnStr))
{
Assert.Throws <InvalidOperationException>(() => { conn.Credential = sqlCredential; });
}
// Verify that connection string with username/password or SSPI may not be set with credential present.
using (var conn = new SqlConnection(string.Empty, sqlCredential))
{
Assert.Throws <InvalidOperationException>(() => { conn.ConnectionString = cleartextCredsConnStr; });
Assert.Throws <InvalidOperationException>(() => { conn.ConnectionString = sspiConnStr; });
}
}
/// <summary>
/// Creates a SQLConnection instance from database connection settings. Caller should threat
/// this method like a call to new SqlConnection() by disposing of the instance appropriately.
/// </summary>
/// <param name="settings">The database connection settings.</param>
/// <returns>A SQLConnection instance configured with the given database connection settings.</returns>
public static SqlConnection CreateSqlConnection(this DatabaseConnectionSettings settings)
{
if (settings == null)
{
throw new ArgumentNullException("settings");
}
var connectionString = settings.CreateCredentiallessConnectionString();
if (settings.Credentials != null)
{
if (!string.IsNullOrWhiteSpace(settings.MoreOptions))
{
connectionString.Append(settings.MoreOptions);
}
var credentials = new SqlCredential(settings.Credentials.User, settings.Credentials.Password);
return(new SqlConnection(connectionString.ToString(), credentials));
}
connectionString.Append("Integrated Security=True;");
if (!string.IsNullOrWhiteSpace(settings.MoreOptions))
{
connectionString.Append(settings.MoreOptions);
}
return(new SqlConnection(connectionString.ToString()));
}
private void backgroundWorker1_DoWork(object sender, System.ComponentModel.DoWorkEventArgs e)
{
SqlCredential user = null;
if (radioButton2.Checked)
{
if (passwordBox.Text.Length <= 0)
{
toolStripStatusLabel1.Text = "Prázdné heslo!";
return;
}
SecureString secure = new SecureString();
foreach (char c in passwordBox.Text)
{
secure.AppendChar(c);
}
user = new SqlCredential(userNameBox.Text, secure);
}
myConnection = new SqlConnection("Server=" + ipAddressBox.Text + ";database=" + dbNameBox.Text + ";Trusted_Connection=yes", user);
try
{
myConnection.Open();
toolStripStatusLabel1.Text = "Úspěšně připojeno k databázi!";
}
catch (SqlException ex)
{
toolStripStatusLabel1.Text = "Nepovedlo se připojit k databázi!";
}
}
public SQLServerWrapper(string connectionString, SqlCredential credentials, WrapperAdditionalConfig config) : this()
{
Connection = new SqlConnection(connectionString, credentials);
if (!string.IsNullOrWhiteSpace(config.ParameterBindingStartValue))
{
BindingStartValue = config.ParameterBindingStartValue;
if (!string.IsNullOrWhiteSpace(config.DatabaseVersion))
{
ParameterProcessor = new SQLServerParameterProcessor(InternalDatabaseBinding, config.DatabaseVersion, config.ParameterBindingStartValue);
}
else
{
ParameterProcessor = new SQLServerParameterProcessor(InternalDatabaseBinding, null, config.ParameterBindingStartValue);
}
}
else
{
if (!string.IsNullOrWhiteSpace(config.DatabaseVersion))
{
ParameterProcessor = new SQLServerParameterProcessor(InternalDatabaseBinding, config.DatabaseVersion);
}
}
MaxDeadlockRetry = config?.MaxDeadlockRetry ?? 0;
MaxDeadlockMSDelay = config?.MaxDeadlockMSDelay ?? 0;
ConnectionTimeout = config?.ConnectionTimeout ?? 30;
}
/// <summary>
/// Ensures that credentials are provided for the given connection string object.
/// </summary>
/// <param name="connectionString">
/// Input connection string object.
/// </param>
/// <param name="parameterName">
/// Parameter name of the connection string object.
/// </param>
/// <param name="secureCredential">
/// Input secure SQL credential object.
/// </param>
/// <param name="accessTokenFactory">
/// The access token factory.
/// </param>
internal static void EnsureCredentials(
SqlConnectionStringBuilder connectionString,
string parameterName,
SqlCredential secureCredential,
Func <string> accessTokenFactory)
{
// Check for integrated authentication
if (connectionString.IntegratedSecurity)
{
return;
}
// Check for active directory integrated authentication (if supported)
if (connectionString.ContainsKey(ShardMapUtils.Authentication))
{
string authentication = connectionString[ShardMapUtils.Authentication].ToString();
if (authentication.Equals(ShardMapUtils.ActiveDirectoryIntegratedStr, StringComparison.OrdinalIgnoreCase) ||
authentication.Equals(ShardMapUtils.ActiveDirectoryInteractiveStr, StringComparison.OrdinalIgnoreCase))
{
return;
}
}
// If secure credential not specified, verify that user/pwd are in the connection string. If secure credential
// specified, verify user/pwd are not in insecurely in the connection string.
bool expectUserIdPasswordInConnectionString = secureCredential == null && accessTokenFactory == null;
EnsureHasCredential(
connectionString,
parameterName,
expectUserIdPasswordInConnectionString);
}
// <summary>Build a new SqlConnection object</summary>
// <param name="ConnectionString" type="string">The connection string to utilize in the new SqlConnection object</param>
// <param name="Username" type="string">The username of credentails to utilize in the new SqlConnection object</param>
// <param name="Password" type="SecureString">The ciphertext password of credentials to utilize in the new SqlConnection object</param>
// <return>SqlConnection</return>
public static SqlConnection New(string ConnectionString, string Username, System.Security.SecureString Password)
{
// Make a SqlCredential using the provided Username and ciphertext Password
var Credential = new SqlCredential(Username, Password);
return(new SqlConnection(ConnectionString, Credential)); // Return new SqlConnection using ConnectionString and Credential
}
public NppesController(IConfiguration config, IWebHostEnvironment env, ILogger <NppesController> logger)
{
_logger = logger;
_config = config;
_env = env;
// Create SQL Server credential
string txtPwd = config["Sqlserver:password"];
if (txtPwd == null)
{
string errmsg = "Unable to obtain SQL Server Password from application configuration - Exiting Program";
_logger.LogError(errmsg);
throw new System.ArgumentNullException("Sqlserver:password", errmsg);
}
SecureString secureStringPwd = new SecureString();
txtPwd.ToCharArray().ToList().ForEach(p => secureStringPwd.AppendChar(p));
secureStringPwd.MakeReadOnly();
string txtUserId = config["Sqlserver:userid"];
if (txtUserId == null)
{
string errmsg = "Unable to obtain SQL Server User ID from application configuration - Exiting Program";
_logger.LogError(errmsg);
throw new System.ArgumentNullException("Sqlserver:userid", errmsg);
}
_sqlcred = new SqlCredential(txtUserId, secureStringPwd);
// Get SQL Server connection string
_connstr = _config.GetSection("ConnectionStrings")["DefaultConnection"];
}
/// <summary>
/// Asynchronously opens a regular <see cref="SqlConnection"/> to the shard
/// to which the specified key value is mapped.
/// </summary>
/// <typeparam name="TKey">Type of the key.</typeparam>
/// <param name="key">Input key value.</param>
/// <param name="connectionString">
/// Connection string with credential information such as SQL Server credentials or Integrated Security settings.
/// The hostname of the server and the database name for the shard are obtained from the lookup operation for key.
/// </param>
/// <param name="secureCredential">Secure SQL Credential.</param>
/// <param name="options">Options for validation operations to perform on opened connection.</param>
/// <returns>A Task encapsulating an opened SqlConnection.</returns>
/// <remarks>
/// Note that the <see cref="SqlConnection"/> object returned by this call is not protected against transient faults.
/// Callers should follow best practices to protect the connection against transient faults
/// in their application code, e.g., by using the transient fault handling
/// functionality in the Enterprise Library from Microsoft Patterns and Practices team.
/// This call only works if there is a single default mapping.
/// </remarks>
public Task <SqlConnection> OpenConnectionForKeyAsync <TKey>(
TKey key,
string connectionString,
SqlCredential secureCredential,
ConnectionOptions options)
{
ExceptionUtils.DisallowNullArgument(connectionString, "connectionString");
Debug.Assert(this.StoreShardMap.KeyType != ShardKeyType.None);
using (ActivityIdScope activityIdScope = new ActivityIdScope(Guid.NewGuid()))
{
IShardMapper <TKey> mapper = this.GetMapper <TKey>();
if (mapper == null)
{
throw new ArgumentException(
StringUtils.FormatInvariant(
Errors._ShardMap_OpenConnectionForKey_KeyTypeNotSupported,
typeof(TKey),
this.StoreShardMap.Name,
ShardKey.TypeFromShardKeyType(this.StoreShardMap.KeyType)),
"key");
}
Debug.Assert(mapper != null);
return(mapper.OpenConnectionForKeyAsync(
key,
new SqlConnectionInfo(
connectionString,
secureCredential),
options));
}
}
/// <summary>
/// Constructs a new instance of store connection.
/// </summary>
/// <param name="kind">Type of store connection.</param>
/// <param name="connectionString">Connection string for store.</param>
/// <param name="secureCredential">Secure Credential for store.</param>
/// <returns>An unopened instance of the store connection.</returns>
public virtual IStoreConnection GetConnection(
StoreConnectionKind kind,
string connectionString,
SqlCredential secureCredential)
{
return(new SqlStoreConnection(kind, connectionString, secureCredential));
}
/// <summary>
/// Alternate method to login
/// SqlCredential Class
/// https://docs.microsoft.com/en-us/dotnet/api/system.data.sqlclient.sqlcredential?view=netframework-4.8
///
/// See full working method below.
/// </summary>
public void SqlCredentialExample()
{
var ConnectionString = "Data Source=.\\SQLEXPRESS;Initial Catalog=UserLoginExample";
var userName = "******";
var password = "******";
var securePassword = new SecureString();
foreach (var character in password)
{
securePassword.AppendChar(character);
}
securePassword.MakeReadOnly();
var credentials = new SqlCredential(userName, securePassword);
using (var cn = new SqlConnection {
ConnectionString = ConnectionString
})
{
cn.Credential = credentials;
cn.Open();
}
}
/// <summary>
/// Asynchronously a regular <see cref="SqlConnection"/> to the specified shard.
/// </summary>
/// <param name="connectionString">
/// Connection string with credential information such as SQL Server credentials or Integrated Security settings.
/// The hostname of the server and the database name for the shard are obtained from the lookup operation for key.
/// </param>
/// <param name="secureCredential">Secure SQL Credential.</param>
/// <param name="options">Options for validation operations to perform on opened connection.</param>
/// <returns>A Task encapsulating an opened SqlConnection</returns>
/// <remarks>
/// Note that the <see cref="SqlConnection"/> object returned by this call is not protected against transient faults.
/// Callers should follow best practices to protect the connection against transient faults in their application code, e.g., by using the transient fault handling
/// functionality in the Enterprise Library from Microsoft Patterns and Practices team.
/// All non-usage errors will be propagated via the returned Task.
/// </remarks>
public Task <SqlConnection> OpenConnectionAsync(string connectionString, SqlCredential secureCredential, ConnectionOptions options)
{
using (ActivityIdScope activityIdScope = new ActivityIdScope(Guid.NewGuid()))
{
return(this.ShardMap.OpenConnectionAsync(this as IShardProvider, connectionString, secureCredential, options));
}
}
private void SetProvider(string ConnectionString, int CommandTimeout, SqlCredential Credential = null)
{
this.CommandTimeout = CommandTimeout;
if (Credential == null)
{
Provider = new SqlConnection(ConnectionString);
}
else
{
Provider = new SqlConnection(ConnectionString, Credential);
}
SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder(ConnectionString);
bool MultipleActiveResultSets = builder.MultipleActiveResultSets;
if (MultipleActiveResultSets)
{
IsMultiCommand = true;
}
else
{
Command = Provider.CreateCommand();
}
if (Command != null)
{
Command.CommandTimeout = CommandTimeout;
}
}
public SqlConnection GetOpenSqlConnection(SqlCredential credential)
{
var connection = new SqlConnection(_connectionString, credential);
OpenConnection(connection);
return(connection);
}
public bool SqlCredentialLogin(string pUserName, string pPassword)
{
string connectionString = $"Data source={serverName};" +
$"Initial catalog={catalogName};";
var securePassword = new SecureString();
foreach (var character in pPassword)
{
securePassword.AppendChar(character);
}
securePassword.MakeReadOnly();
var credentials = new SqlCredential(pUserName, securePassword);
using (SqlConnection cn = new SqlConnection {
ConnectionString = connectionString
})
{
try
{
cn.Credential = credentials;
cn.Open();
return(true);
}
catch (Exception e)
{
return(true);
}
}
}
/// <summary>
/// Answer a new instance of a <see cref="IDbConnection"/>
/// </summary>
/// <returns>A instance of <see cref="IDbConnection"/> connection</returns>
/// <exception cref="InvalidOperationException"></exception>
public override IDbConnection Connection()
{
try
{
var secureString = new SecureString();
foreach (var c in Password)
{
secureString.AppendChar(c);
}
var sqlCredentials = new SqlCredential(Username, secureString);
var sqlConnectionStringBuilder = new SqlConnectionStringBuilder
{
InitialCatalog = DatabaseName,
Encrypt = UseSsl
};
var connection = new SqlConnection(sqlConnectionStringBuilder.ConnectionString, sqlCredentials);
return(connection);
}
catch (Exception e)
{
throw new InvalidOperationException(
$"{GetType().Name}: Cannot connect because database unavailable or wrong credentials.", e);
}
}
// GET: api/Poll
public IEnumerable <Poll> Get()
{
List <Poll> retList = new List <Poll>();
var pw = Util.ConvertToSecureString("Brinter3.1415");
SqlCredential credential = new SqlCredential("Ronaldo", pw);
using (SqlConnection conn = new SqlConnection(ConnectString))
{
conn.Open();
SqlCommand cmd = new SqlCommand("SELECT * FROM Polls;", conn);
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
Poll poll = new Poll();
poll.ID = (int)reader[0];
poll.Name = (string)reader[1];
poll.Description = (string)reader[2];
poll.PollType = (string)reader[3];
poll.IsActive = (bool)reader[4];
poll.EndTime = (DateTime)reader[5];
poll.IsVotingRestricted = (bool)reader[6];
retList.Add(poll);
}
}
return(retList);
}
/// <summary>
/// Create a table only when the table does not currently exists.
/// If the user does not have permissions this will fail. Permissions
/// are set under the security table in Object Explorer. Select the database
/// then set the permissions.
/// </summary>
/// <param name="pUserNName"></param>
/// <param name="pPassword"></param>
/// <returns></returns>
public string DoWork(string pUserNName, string pPassword)
{
string connectionString = $"Data Source={serverName};" +
$"Initial Catalog={catalogName};";
var securePassword = new SecureString();
foreach (var character in pPassword)
{
securePassword.AppendChar(character);
}
securePassword.MakeReadOnly();
var credentials = new SqlCredential(pUserNName, securePassword);
using (var cn = new SqlConnection {
ConnectionString = connectionString
})
{
using (var cmd = new SqlCommand {
Connection = cn
})
{
try
{
// Table name to create
var tableName = "Person";
cmd.CommandText = "SELECT CASE WHEN exists((SELECT * FROM information_schema.tables " +
$"WHERE table_name = '{tableName}')) THEN 1 ELSE 0 END;";
cn.Credential = credentials;
cn.Open();
// check to see if table currently exists, if not create the table
var exists = (int)cmd.ExecuteScalar() == 1;
if (exists == false)
{
/*
* Must have db_owner permissions to database
*/
cmd.CommandText = "CREATE TABLE Person (PersonIdentifier INT PRIMARY KEY," +
"FirstName VARCHAR(50) NOT NULL," +
"LastName VARCHAR(50) NOT NULL);";
cmd.ExecuteNonQuery();
}
return("");
}
catch (Exception e)
{
return(e.Message);
}
}
}
}
private void btnSubmit_Click(object sender, EventArgs e)
{
try
{
using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["Northwind"].ConnectionString))
{
securePassword.MakeReadOnly();
SqlCredential credentials = new SqlCredential(txtUserName.Text, securePassword);
conn.Credential = credentials;
conn.Open();
SqlCommand cmd = new SqlCommand("Select * From Categories", conn);
SqlDataReader reader = cmd.ExecuteReader();
DataTable table = new DataTable();
table.Load(reader);
dgvCategories.DataSource = table;
}
}
catch (SqlException ex)
{
MessageBox.Show(ex.Message);
txtUserName.Text = string.Empty;
txtPassword.Text = string.Empty;
txtUserName.Focus();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
private void OpenConnection(string connectionString = "", SqlCredential credential = null)
{
if (null == Connection)
{
if (string.IsNullOrWhiteSpace(connectionString))
{
connectionString = DataAccessConfiguration.SqlConnectionString;
}
Connection = new SqlConnection(connectionString);
}
else
if (!(Connection.State == ConnectionState.Open))
{
if (!(Connection.State == ConnectionState.Open))
{
while (Connection.State == ConnectionState.Connecting)
{
}
}
}
if (!(Connection.State == ConnectionState.Open))
{
Connection.Open();
}
}
private static ShardMapManager GetSqlShardMapManager(
string connectionString,
SqlCredential secureCredential,
ShardMapManagerLoadPolicy loadPolicy,
RetryBehavior retryBehavior,
EventHandler <RetryingEventArgs> retryEventHandler,
bool throwOnFailure)
{
Debug.Assert(connectionString != null);
Debug.Assert(retryBehavior != null);
SqlShardMapManagerCredentials credentials = new SqlShardMapManagerCredentials(connectionString, secureCredential);
StoreOperationFactory storeOperationFactory = new StoreOperationFactory();
IStoreResults result;
TransientFaultHandling.RetryPolicy retryPolicy = new TransientFaultHandling.RetryPolicy(
new ShardManagementTransientErrorDetectionStrategy(retryBehavior),
RetryPolicy.DefaultRetryPolicy.GetRetryStrategy());
EventHandler <TransientFaultHandling.RetryingEventArgs> handler = (sender, args) =>
{
if (retryEventHandler != null)
{
retryEventHandler(sender, new RetryingEventArgs(args));
}
};
try
{
retryPolicy.Retrying += handler;
using (IStoreOperationGlobal op = storeOperationFactory.CreateGetShardMapManagerGlobalOperation(
credentials,
retryPolicy,
throwOnFailure ? "GetSqlShardMapManager" : "TryGetSqlShardMapManager",
throwOnFailure))
{
result = op.Do();
}
}
finally
{
retryPolicy.Retrying -= handler;
}
return(result.Result == StoreResult.Success ?
new ShardMapManager(
credentials,
new SqlStoreConnectionFactory(),
storeOperationFactory,
new CacheStore(),
loadPolicy,
RetryPolicy.DefaultRetryPolicy,
retryBehavior,
retryEventHandler) :
null);
}
public SqlServerDatabase(string name, string connectionString, string user, SecureString pwd) : base(name)
{
_connectionString = connectionString;
_credential = new SqlCredential(user, pwd);
_lazyConnStrBuilder = new Lazy <SqlConnectionStringBuilder>(
() => new SqlConnectionStringBuilder(_connectionString));
}
protected virtual IDbConnection CreateConnection(string connectionString = null)
{
SqlCredential sqlCredential = CreateSqlCredential(CustomCredentialInstance);
var connection = new SqlConnection(connectionString ?? ConnectionString, sqlCredential);
connection.Open();
return(connection);
}
private void OpenConnection(string connectionString = "", SqlCredential credential = null)
{
if (null == Connection)
{
Connection = new SqlConnection(string.IsNullOrWhiteSpace(connectionString) ? DataAccessConfiguration.SqlConnectionString : connectionString);
}
OpenConnection();
}
/// <summary>
/// Creates an instance of <see cref="SqlConnectionInfo"/>.
/// </summary>
/// <param name="connectionString">The connection string.</param>
/// <param name="secureCredential">The secure SQL credential.</param>
internal SqlConnectionInfo(
string connectionString,
SqlCredential secureCredential)
{
ExceptionUtils.DisallowNullArgument(connectionString, "connectionString");
this.ConnectionString = connectionString;
this.Credential = secureCredential;
}
public SqlConnection OpenConnectionForKey(TKey key, string connectionString, SqlCredential secureCredential, ConnectionOptions options)
{
ExceptionUtils.DisallowNullArgument(connectionString, "connectionString");
using (ActivityIdScope activityIdScope = new ActivityIdScope(Guid.NewGuid()))
{
return(this.rsm.OpenConnectionForKey(key, connectionString, secureCredential, options));
}
}
public DataProvider(string connectionString = null, string accessToken = null, SqlCredential credential = null, bool fireInfoMessageEventOnUserErrors = false, ISite site = null, bool statisticsEnabled = false)
{
ConnectionString = connectionString;
AccessToken = accessToken;
Credential = credential;
FireInfoMessageEventOnUserErrors = fireInfoMessageEventOnUserErrors;
Site = site;
StatisticsEnabled = statisticsEnabled;
}
