title: How to change credentials in a CLR function to use an account other than the service account
body: <p>When running code under the CLR it runs as the SQL Service Account, which does not have access to the resource I need. I don't know a vast amount of programming with credentials. How do I switch credentials to use a different account that does?</p>
  
  <p>CLR code runs under the SQL Service Account. We don't want to give permissions to this account, but rather use another account to provide access to the external resources the CLR functions require (files mostly). How do you switch account during processing, and then revert back?</p>
  
  <p>Note that the class is for a table-valued function, so SQL will read each row using the foreach (IEnumerator) method, during which the file needs to remain open. So, do I need to switch credentials each time I read a line from the file and switch back, or just for the file open?</p>
  
  <p>Lastly, if I switch credentials, am I just changing it for my thread, or for the whole of SQL!? Clearly I don't want to do the latter. I need something a bit like SQL impersonation just for when executed my CLR function, reverting back when returning to SQL.</p>
  
  <p>Example CLR to load a textfile. We need to change credential before opening the file, reading it and closing it.</p>
  
  <pre><code>using System;
  using System.Collections;
  using System.Collections.Generic;
  using System.Data;
  using System.Data.Sql;
  using System.Data.SqlClient;
  using System.Data.SqlTypes;
  using System.IO;
  using System.Linq;
  using System.Runtime.InteropServices;
  using System.Text;
  using Microsoft.SqlServer.Server;
  
  
  
  // TextLine and TextFile load text files with indexed lines
  public class TextLine
  // contains a line of text from a file
  {
      int _lineIndex;
      string _data;
  
  
      public TextLine(int iIndex, string cData)
      {
          _lineIndex = iIndex;
          _data = cData;
      }
  
      public int LineIndex
      {
          get { return _lineIndex; }
          set { _lineIndex = value; }
      }
  
      public string Data
      {
          get { return _data; }
          set { _data = value; }
      }
  }
  
  
  public class TextFile : IEnumerable, IEnumerator
  {
      private int _index = -1;
      private string _data; // the content of the last line read
      private StreamReader _reader = null;
  
  
      public TextFile(string filename)
      {
          _reader = new StreamReader(filename);
      }
  
  
      IEnumerator IEnumerable.GetEnumerator()
      {
          return this;
      }
  
  
      public object Current
      {
          get 
          {
              return new TextLine(_index, _data);
          }
      }
  
  
      public bool MoveNext()
      {
          _data = _reader.ReadLine();
          if (_data != null)
          {
              _index++;
              return true;
          }
          else
          {
              _reader.Close();
              return false;
          }
      }
  
  
      public void Reset()
      {
          _index = -1;
      }
  }
  
  
  public partial class UserDefinedFunctions
  {
      [SqlFunction(
          Name = "ReadTextFile",
          FillRowMethodName = "ReadTextFile_FillRow",
          TableDefinition = "LineIndex INT, Data NVARCHAR(4000)")]
  
  
      public static IEnumerable ReadTextFile(SqlString cFilename) // this returns an object that has inherited IEnumerable
      {
          TextFile oFile = null;
  
          try
          {
              oFile = new TextFile((string)cFilename);
              return oFile;
          }
  
          finally
          {
              oFile.Reset();
          }
      }
  
      public static void ReadTextFile_FillRow(object row, out SqlInt32 LineIndex, out SqlString Data)
      {
          TextLine oLine;
  
          oLine = (TextLine)row;
          LineIndex = (SqlInt32)oLine.LineIndex;
          Data = (SqlString)oLine.Data;
      }
  }
  </code></pre>
  
  <p>I need just my CLR code to run under a specified AD account, not the SQL Service account.</p>