CODEWITHSUNDEEP
c#sqlconnection
DCruz22
DCruz22

Reputation: 806

Change the SqlConnection,Credentials property

So i have a login page and i have to implement a validation: 'If the user is "sa" then it should connect to the Database using that users credentials', which means i have to modify the credentials of my SqlConnection object:

        if(username.Equals("sa", StringComparison.OrdinalIgnoreCase))
        {
            var securePassword = new System.Security.SecureString();
            foreach(char character in password)
            {
                securePassword.AppendChar(character);
            }

            securePassword.MakeReadOnly();
            var credentials = new SqlCredential(username, securePassword);
            sqlConn.Close();
            sqlConn.Credential = credentials;

            sqlConn.Open();

            return true;
        }

But im getting an exception in sqlConn.Credential = credentials; even though the property is not readonly

InvalidOperationException: Cannot use Credential with UserID, UID, Password, or PWD connection string keywords.

Is there any other way to change the Credentials property?

Thanks in advance.

Upvotes: 3

Views: 13704

Answers (3)

Mike Debela
Mike Debela

Reputation: 1950

Or you could use SqlConnectionStringBuilder,

string connectString = "Data source=.;initial catalog=yourDB; 
                        User Id=user; Password=password";
SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder (connectString);
SqlConnection sqlConn= new SqlConnection(builder.ConnectionString);

Then in your method,

 builder.Remove("User Id");
 builder.Remove("Password");
 // builder.Remove("integrated security"); if you had used
 sqlConn.ConnectionString = builder.ConnectionString;

Upvotes: 5

kirodge
kirodge

Reputation: 688

SqlConnection.Credential Property

From https://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlconnection.credential(v=vs.110).aspx

An InvalidOperationException exception will be raised:

  • If Credential is set on an open connection.
  • If Credential is set when Context Connection=true.
  • If Credential is set when Integrated Security = true.
  • If Credential is set when the connection string uses Password.
  • If Credential is set when the connection string uses UserID.

Upvotes: 5

DCruz22
DCruz22

Reputation: 806

It seems like you can modify the credentials if the SqlConnection has an user and password. I use the examples of this tutorial to make it work:

        if (username.Equals("sa", StringComparison.OrdinalIgnoreCase))
        {
            using (SqlConnection conn = new SqlConnection(string.Format("Server={0};Initial Catalog={1};", sqlConn.DataSource, sqlConn.Database)))
            {
                var securePassword = new System.Security.SecureString();
                foreach (char character in password)
                {
                    securePassword.AppendChar(character);
                }

                securePassword.MakeReadOnly();

                var credentials = new SqlCredential(username, securePassword);
                conn.Credential = credentials;

                conn.Open();

                return true;
            }
        }

Since i couldn't modify the Credential property i made a copy of my SqlConnection object without the username and password in the connectionString. That way i can add new Credential later. Hope this helps anyone else facing a similar problem.

Upvotes: 1

Related Questions