I am running a Windows WCF service using local system account and from within the service we are trying to connect to SQL Server instance using SMO with domain user account.
The code shown below works fine is I am using SMO from SQL Server 2014 SDK, but if I use SQL Server 2016 CTP 3.2 SMO then it throws an exception saying that
Login Failed for user {domain}{local machine name}
wherein I have given domain admin credentials in server connection object.
var srvConnectionConn = new ServerConnection("WIN-DVGQDI73QR6");
srvConnectionConn.ConnectAsUser = true;
srvConnectionConn.ConnectAsUserName = @"administrator@mva";
srvConnectionConn.ConnectAsUserPassword = "*****";
srvConnectionConn.ConnectTimeout = 60;
srvConnectionConn.NonPooledConnection = true;
Server srv = new Server(srvConnectionConn);
srv.SetDefaultInitFields(typeof(LogFile), "Name", "FileName");
srv.SetDefaultInitFields(typeof(FileGroup), "Name");
srv.SetDefaultInitFields(typeof(DataFile), "Name", "FileName");
Database db = srv.Databases["DB_H"];
bool bRemote = db.RemoteDataArchieveEnabled;
Note: if I run my windows service through domain admin account instead if local system account then the below code works fine w/o having any exception.
It looks like SQL 2016 SMO binaries are not able to impersonate the given user account.
Regards
Ashish
The above mentioned code started working as expected in SQL 2016 RC3 release. I guess there was a bug in SQL 2016 CTP3.2 which was causing this error in impersonating the user account.
~Ashish