mono/mono — pull request #1295 by nealef (2014-09-19 16:15:46+00:00)
Title: Add SqlCredential support to SqlClient/Tds

Currently mono doesn't support SqlCredentials meaning that connection to SqlServer databases that don't use domain logins require passwords in plain text. This commit adds SqlCredential support so that application writers may hold password data in SecureStrings. In addition, the SqlClient and Tds code also uses SecureStrings when dealing with passwords up until the point they need to be transmitted to the server.

The changes consist of -

- Tds.cs: Use SecureString objects for passwords; Add method to retrieve string from SecureString
- Tds42.cs: Use SecureString for passwords
- Tds50.cs: Use SecureString for passwords
- Tds70.cs: Use SecureString for passwords
- TdsConnectionParameters.cs: Use SecureString for passwords; Initialize Password parameter as cleared string; Add indicator for when password is set
- SqlConnection.cs: Use SecureString for passwords; Add SqlConnect method that accepts an SqlCredential along with the Connection string; Perform checking of parameters to ensure user/password not specified in connection string if credentials have been specified or using credentials when domain login is specified.
- SqlCredential.cs: Add new class with support for credentials
- System.Data.dll.sources: Add SqlCredential.cs to the build list

The following code was used to test the changes. Note that I manually changed the connection string and credential information to test the various permutations in order to check the parameter validation etc. as described in http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcredential(v=vs.110).aspx

This program doesn't use credentials and is used to prime the database that must exist on the server and be known as "test01" and have a user named test01 defined with the password mmmmQ69!

using System;
using System.Data;
using System.Data.SqlClient;
using Mono.Data.Tds;

public class TestSqlConnection
{
public static void Main(string[] args)
{
SqlConnection Conn;
Conn = new SqlConnection("Server=example.com;Database=Test;" +
"Trusted_Connection=false;UID=test01;PWD=mmmmQ69!");
try
{
Conn.Open();
}
catch (SqlException ex)
{
// Connection failed
Console.WriteLine(ex.Message);
return;
}

string[] DropTableSQL = {"drop table emp", "drop table dept"};
for (int x=0; x<=1; x++)
{
try
{
// Drop the tables, don't care if they don't exist
SqlCommand DBCmd = new SqlCommand(DropTableSQL[x], Conn);
DBCmd.ExecuteNonQuery();
}
catch (SqlException ex)
{
Console.WriteLine(ex.Message);
return;
}
}

// Create the tables
string CreateEmpTableSQL = "CREATE TABLE emp" +
"(empno INT PRIMARY KEY NOT NULL," +
"ename VARCHAR(10) NOT NULL," +
"job VARCHAR(9) NOT NULL," +
"mgr INT," +
"hiredate DATETIME NOT NULL," +
"sal NUMERIC(7,2) NOT NULL," +
"comm NUMERIC(7,2)," +
"dept INT NOT NULL)";

string CreateDeptTableSQL = "CREATE TABLE dept (" +
"deptno INT NOT NULL," +
"dname VARCHAR(14)," +
"loc VARCHAR(13))";
try
{
SqlCommand DBCmd = new SqlCommand(CreateEmpTableSQL, Conn);
DBCmd.ExecuteNonQuery();
DBCmd.CommandText = CreateDeptTableSQL;
DBCmd.ExecuteNonQuery();
}
catch (Exception ex)
{
//Create tables failed
Console.WriteLine (ex.Message);
return;
}

// Now insert the records

string[] InsertEmpRecordsSQL = {
"insert into emp values " +
"(1,'JOHNSON','ADMIN',6,'12-17-1990',18000,NULL,4)",
"insert into emp values " +
"(2,'HARDING','MANAGER',9,'02-02-1998',52000,300,3)",
"insert into emp values " +
"(3,'TAFT','SALES I',2,'01-02-1996',25000,500,3)",
"insert into emp values " +
"(4,'HOOVER','SALES I',2,'04-02-1990',27000,NULL,3)",
"insert into emp values " +
"(5,'LINCOLN','TECH',6,'06-23-1994',22500,1400,4)",
"insert into emp values " +
"(6,'GARFIELD','MANAGER',9,'05-01-1993',54000,NULL,4)",
"insert into emp values " +
"(7,'POLK','TECH',6,'09-22-1997',25000,NULL,4)",
"insert into emp values " +
"(8,'GRANT','ENGINEER',10,'03-30-1997',32000,NULL,2)",
"insert into emp values " +
"(9,'JACKSON','CEO',NULL,'01-01-1990',75000,NULL,4)",

"insert into emp values " +
"(10,'FILLMORE','MANAGER',9,'08-09-1994',56000, NULL,2)",
"insert into emp values " +
"(11,'ADAMS','ENGINEER',10,'03-15-1996',34000, NULL,2)",
"insert into emp values " +
"(12,'WASHINGTON','ADMIN',6,'04-16-1998',18000,NULL,4)",
"insert into emp values " +
"(13,'MONROE','ENGINEER',10,'12-03-2000',30000,NULL,2)",
"insert into emp values " +
"(14,'ROOSEVELT','CPA',9,'10-12-1995',35000,NULL,1)"};

string[] InsertDeptRecordsSQL = {
"insert into dept values (1,'ACCOUNTING','ST LOUIS')",
"insert into dept values (2,'RESEARCH','NEW YORK')",
"insert into dept values (3,'SALES','ATLANTA')",
"insert into dept values (4,'OPERATIONS','SEATTLE')"};

// Insert dept table records first
for (int x = 0; x