public static void SendTransactionCommit(this TdsPackageWriter writer, long sqlTransactionId)
{
WriteTransactionHeader(writer, TdsEnums.TransactionManagerRequestType.Commit, sqlTransactionId);
writer.WriteByte(0); // No xact name
writer.WriteByte(0); // No flags
writer.SendLastMessage();
}
public static void WriteParameterLen(this TdsPackageWriter writer, TdsMetaType.MetaDataWrite metaType, int size, bool isNull)
{
if (isNull)
{
return;
}
//write length of the parameter
if (metaType.IsPlp)
{
writer.WriteInt64(size);
}
else if (metaType.IsLong)
{
// text/image/SQLVariant have a 4 byte length, plp datatypes have 8 byte lengths
writer.WriteInt32(size);
}
else if (metaType.NullableType == TdsEnums.SQLDATETIME2 || metaType.NullableType == TdsEnums.SQLTIME || metaType.NullableType == TdsEnums.SQLDATETIMEOFFSET)
{
writer.WriteByte((byte)size);
}
else if (metaType.NullableType == TdsEnums.SQLBIGVARBINARY || metaType.NullableType == TdsEnums.SQLBIGCHAR || metaType.NullableType == TdsEnums.SQLNCHAR || metaType.NullableType == TdsEnums.SQLNVARCHAR) //skip varchar
{
writer.WriteInt16(size);
}
else
{
writer.WriteByte((byte)size); // 1 byte for everything else
}
}
public static void SendTransactionBegin(this TdsPackageWriter writer, TdsEnums.TransactionManagerIsolationLevel isoLevel)
{
var transactionName = "";
WriteTransactionHeader(writer, TdsEnums.TransactionManagerRequestType.Begin, 0);
writer.WriteByte((byte)isoLevel);
writer.WriteByte((byte)(transactionName.Length * 2)); // Write number of bytes (unicode string).
writer.WriteUnicodeString(transactionName);
writer.SendLastMessage();
}
public static void SendTransactionRollback(this TdsPackageWriter writer, long sqlTransactionId)
{
var transactionName = "";
WriteTransactionHeader(writer, TdsEnums.TransactionManagerRequestType.Rollback, sqlTransactionId);
writer.WriteByte((byte)(transactionName.Length * 2)); // Write number of bytes (unicode string).
writer.WriteUnicodeString(transactionName);
writer.WriteByte(0); // No flags
writer.SendLastMessage();
}
public static void WriteTypeInfoLen(this TdsPackageWriter writer, TdsMetaType.MetaDataWrite metaType, int size, bool isNull)
{
//write length or isNull information about the type
if (metaType.IsPlp)
{
if (isNull)
{
writer.WriteInt64(unchecked ((long)TdsEnums.SQL_PLP_NULL));
}
else
{
writer.WriteInt64(size);
}
}
else if (metaType.IsLong)
{
// text/image/SQLVariant have a 4 byte length, plp datatypes have 8 byte lengths
if (isNull)
{
writer.WriteInt32(unchecked ((int)TdsEnums.VARLONGNULL));
}
else
{
writer.WriteInt32(size);
}
}
else if (metaType.NullableType == TdsEnums.SQLDATETIME2 || metaType.NullableType == TdsEnums.SQLTIME || metaType.NullableType == TdsEnums.SQLDATETIMEOFFSET)
{
if (isNull)
{
writer.WriteByte(TdsEnums.FIXEDNULL);
}
else
{
writer.WriteByte((byte)size);
}
}
else if (metaType.NullableType == TdsEnums.SQLBIGVARBINARY || metaType.NullableType == TdsEnums.SQLBIGCHAR || metaType.NullableType == TdsEnums.SQLNCHAR || metaType.NullableType == TdsEnums.SQLNVARCHAR) //skip varchar
{
// non-long but variable length column, must be a BIG* type: 2 byte length
writer.WriteInt16(isNull ? TdsEnums.VARNULL : size);
}
else
{
if (isNull)
{
writer.WriteByte(TdsEnums.FIXEDNULL);
}
else
{
writer.WriteByte((byte)size); // 1 byte for everything else
}
}
}
public static void WriteByteLenString(this TdsPackageWriter writer, string?s)
{
if (string.IsNullOrEmpty(s))
{
writer.WriteByte(0);
}
else
{
writer.WriteByte(checked ((byte)s.Length));
writer.WriteUnicodeString(s);
}
}
public static void WriteCollation(this TdsPackageWriter writer, SqlCollations?collation)
{
if (collation == null)
{
writer.WriteByte(0);
}
else
{
writer.WriteByte(sizeof(uint) + sizeof(byte));
writer.WriteUInt32(collation.Info);
writer.WriteByte(collation.SortId);
}
}
public static void WriteValue(TdsPackageWriter writer, object?value, TdsMetaType.MetaDataWrite metaData, bool isNull)
{
// write the value now
if (isNull)
{
return;
}
switch (value)
{
case string v:
writer.WriteUnicodeString(v);
break;
case decimal v:
writer.WriteSqlDecimal(v, 17);
break;
case bool v:
writer.WriteByte(v ? 1 : 0);
break;
case DateTime v:
writer.WriteDateTime(v);
break;
case byte v:
writer.WriteByte(v);
break;
case short v:
writer.WriteInt16(v);
break;
case int v:
writer.WriteInt32(v);
break;
case long v:
writer.WriteInt64(v);
break;
case float v:
writer.WriteFloat(v);
break;
case double v:
writer.WriteDouble(v);
break;
}
}
public static void SendRpc(this TdsPackageWriter writer, SqlCollations defaultCollation, FormattableString sql, long sqlConnectionId)
{
writer.NewPackage(TdsEnums.MT_RPC);
writer.WriteMarsHeader(sqlConnectionId);
writer.WriteInt16(0xffff);
writer.WriteInt16(TdsEnums.RPC_PROCID_EXECUTESQL);
// Options
writer.WriteInt16(TdsEnums.RPC_PARAM_DEFAULT);
// Stream out parameters
var parameters = CreateParameters(sql);
foreach (var parameter in parameters)
{
// parameters can be unnamed
var param = parameter;
var value = parameter.Value;
var isNull = value == null;
writer.WriteByteLenString(param.Name);
// Write parameter status
writer.WriteByte(parameter.Status);
var p = parameter;
var mt = p.MetaData;
WriteTdsTypeInfo(writer, mt, p.Size, p.IsNull, defaultCollation, p.Scale);
WriteValue(writer, value, mt, isNull);
}
writer.SendLastMessage();
}
private static void WriteOptions(TdsPackageWriter writer, string instanceName, bool marsOn)
{
for (var option = (int)PreLoginOptions.VERSION; option < (int)PreLoginOptions.NUMOPT; option++)
{
switch (option)
{
case (int)PreLoginOptions.VERSION:
// Major and minor
writer.WriteByte(0);
writer.WriteByte(0);
// Build (Big Endian)
writer.WriteByte(0);
writer.WriteByte(0);
// Sub-build (Little Endian)
writer.WriteByte(0);
writer.WriteByte(0);
break;
case (int)PreLoginOptions.ENCRYPT:
writer.WriteByte((byte)EncryptionOptions.NOT_SUP);
break;
case (int)PreLoginOptions.INSTANCE:
foreach (var c in instanceName)
{
writer.WriteByte(c);
}
writer.WriteByte(0); // null terminate
break;
case (int)PreLoginOptions.THREADID:
writer.WriteUInt32(0);
break;
case (int)PreLoginOptions.MARS:
writer.WriteByte((byte)(marsOn ? 1 : 0)); // null terminate
break;
case (int)PreLoginOptions.TRACEID:
writer.WriteByteArray(new byte[GuidSize]);
writer.WriteByteArray(new byte[GuidSize]);
writer.WriteUInt32(0);
break;
}
}
}
private static void WriteSessionRecoveryFeatureRequest(TdsPackageWriter writer, SessionData reconnectData)
{
var(_, initialLength, currentLength, writeState) = SessionRecoveryFeatureRequestLengths(reconnectData);
writer.WriteByte(TdsEnums.FEATUREEXT_SRECOVERY);
if (reconnectData == null)
{
writer.WriteInt32(0);
}
else
{
writer.WriteInt32(8 + initialLength + currentLength); // length of data w/o total length (initial + current + 2 * sizeof(DWORD))
writer.WriteInt32(initialLength);
writer.WriteByteLenString(reconnectData.InitialDatabase);
writer.WriteCollation(reconnectData.InitialCollation);
writer.WriteByteLenString(reconnectData.InitialLanguage);
for (var i = 0; i < SessionData.MaxNumberOfSessionStates; i++)
{
if (reconnectData.InitialState[i] != null)
{
writer.WriteByte((byte)i);
if (reconnectData.InitialState[i].Length < 0xFF)
{
writer.WriteByte((byte)reconnectData.InitialState[i].Length);
}
else
{
writer.WriteByte(0xFF);
writer.WriteInt32(reconnectData.InitialState[i].Length);
}
writer.WriteByteArray(reconnectData.InitialState[i]);
}
}
writer.WriteInt32(currentLength);
writer.WriteByteLenString(reconnectData.Database != reconnectData.InitialDatabase ? reconnectData.Database : null);
writer.WriteCollation(SqlCollations.AreSame(reconnectData.InitialCollation, reconnectData.Collation) ? null : reconnectData.Collation);
writer.WriteByteLenString(reconnectData.Language != reconnectData.InitialLanguage ? reconnectData.Language : null);
for (var i = 0; i < SessionData.MaxNumberOfSessionStates; i++)
{
if (writeState[i])
{
writer.WriteByte((byte)i);
if (reconnectData.Delta[i].DataLength < 0xFF)
{
writer.WriteByte((byte)reconnectData.Delta[i].DataLength);
}
else
{
writer.WriteByte(0xFF);
writer.WriteInt32(reconnectData.Delta[i].DataLength);
}
writer.WriteByteArray(reconnectData.Delta[i].Data);
}
}
}
}
public static void WriteTdsTypeInfo(this TdsPackageWriter writer, TdsMetaType.MetaDataWrite metaData, int size, bool isNull, SqlCollations defaultCollation, byte scale)
{
var mt = metaData;
writer.WriteByte(mt.NullableType);
writer.WriteTypeInfoLen(mt, size, isNull); //typeinfo varlen
if (mt.HasCollation)
{
writer.WriteCollation2(defaultCollation);
}
if (mt.HasPrecision)
{
writer.WriteByte(28); //Max clr precision
}
if (mt.HasScale)
{
writer.WriteByte(scale); //
}
writer.WriteParameterLen(metaData, size, isNull); //len parameter
}
private static void WriteColumn(TdsPackageWriter writer, MetadataBulkCopy md)
{
writer.WriteInt32(0x0);
writer.WriteByte(md.Flag1);
writer.WriteByte(md.Flag2);
writer.WriteByte(md.TdsType);
var mt = TdsMetaType.TdsTypes[md.TdsType];
if (mt.LenBytes == 1)
{
writer.WriteByte(md.Length);
}
if (mt.LenBytes == 2)
{
writer.WriteInt16(md.Length);
}
if (mt.LenBytes == 4)
{
writer.WriteInt32(md.Length);
}
if (mt.HasPrecision)
{
writer.WriteByte(md.Precision);
}
if (mt.HasScale)
{
writer.WriteByte(md.Scale);
}
if (mt.HasCollation)
{
writer.WriteUInt32(md.Collation.Info);
writer.WriteByte(md.Collation.SortId);
}
if (mt.IsTextOrImage)
{
writer.WriteInt16(md.PartTableName.TableName.Length);
writer.WriteUnicodeString(md.PartTableName.TableName);
}
writer.WriteByte((byte)md.Column.Length);
writer.WriteUnicodeString(md.Column);
}
public static void WriteBulkInsertColMetaData(TdsPackageWriter writer)
{
//0x81,0x01,0x00,//colmetadata 0x001 column
//0x00,0x00,0x00,0x00,//usertype ignore
//0x09,0x00, //flags
//0x26,0x04, //int 4
//0x02,0x49,0x00,0x64,0x00, //columnname 2 chars ID
var columns = writer.ColumnsMetadata;
writer.WriteByte(TdsEnums.SQLCOLMETADATA);
writer.WriteInt16(columns.Length);
foreach (var column in columns)
{
WriteColumn(writer, column);
}
}
private static void WriteHeader(TdsPackageWriter writer, string instanceName)
{
// Initialize option offset into buffer buffer
// 5 bytes for each option (1 byte length, 2 byte offset, 2 byte buffer length)
var offset = (int)PreLoginOptions.NUMOPT * 5 + 1;
for (var option = (int)PreLoginOptions.VERSION; option < (int)PreLoginOptions.NUMOPT; option++)
{
// Fill in the option
writer.WriteByte((byte)option);
// Fill in the offset of the option data
writer.WriteByte((byte)((offset & 0xff00) >> 8)); // send upper order byte
writer.WriteByte((byte)(offset & 0x00ff)); // send lower order byte
var optionDataSize = GetOptionSize(option, instanceName);
writer.WriteByte((byte)((optionDataSize & 0xff00) >> 8));
writer.WriteByte((byte)(optionDataSize & 0x00ff));
offset += optionDataSize;
}
writer.WriteByte((byte)PreLoginOptions.LASTOPT);
}
internal static void WriteGlobalTransactionsFeatureRequest(TdsPackageWriter writer)
{
// Write Feature ID
writer.WriteByte(TdsEnums.FEATUREEXT_GLOBALTRANSACTIONS);
writer.WriteInt32(0); // we don't send any data
}
public void WriteSqlBit(bool value, int index)
{
_writer.WriteByte(value ? 1 : 0);
}
public static void WriteCollation2(this TdsPackageWriter writer, SqlCollations collation)
{
writer.WriteUInt32(collation.Info);
writer.WriteByte(collation.SortId);
}
public static void SendTdsLogin(this TdsPackageWriter writer, LoginOptions rec, SessionData recoverySessionData)
{
const string clientInterfaceName = TdsEnums.SQL_PROVIDER_NAME;
var requestedFeatures = rec.RequestedFeatures;
if (recoverySessionData != null && (requestedFeatures & TdsEnums.FeatureExtension.SessionRecovery) == 0)
{
throw new Exception("Recovery session data without session recovery feature request");
}
if (rec.HostName.Length > TdsEnums.MAXLEN_HOSTNAME)
{
throw new Exception("_workstationId.Length exceeds the max length for this value");
}
if (!rec.UseSspi && rec.UserName.Length > TdsEnums.MAXLEN_USERNAME)
{
throw new Exception("_userID.Length exceeds the max length for this value");
}
if (!rec.UseSspi && rec.Password.Length > TdsEnums.MAXLEN_PASSWORD)
{
throw new Exception("_password.Length exceeds the max length for this value");
}
if (rec.ApplicationName.Length > TdsEnums.MAXLEN_APPNAME)
{
throw new Exception("_applicationName.Length exceeds the max length for this value");
}
if (rec.ServerName.Length > TdsEnums.MAXLEN_SERVERNAME)
{
throw new Exception("_dataSource.Length exceeds the max length for this value");
}
if (rec.Language.Length > TdsEnums.MAXLEN_LANGUAGE)
{
throw new Exception("_currentLanguage .Length exceeds the max length for this value");
}
if (rec.Database.Length > TdsEnums.MAXLEN_DATABASE)
{
throw new Exception("_initialCatalog.Length exceeds the max length for this value");
}
if (rec.AttachDbFilename.Length > TdsEnums.MAXLEN_ATTACHDBFILE)
{
throw new Exception("_attachDBFileName.Length exceeds the max length for this value");
}
if (clientInterfaceName.Length > TdsEnums.MAXLEN_CLIENTINTERFACE)
{
throw new Exception("cchCltIntName can specify at most 128 unicode characters. See Tds spec");
}
// get the password up front to use in sspi logic below
var useFeatureExt = requestedFeatures != TdsEnums.FeatureExtension.None;
// set the message type
// length in bytes
var length = TdsEnums.YUKON_LOG_REC_FIXED_LEN;
// add up variable-len portions (multiply by 2 for byte len of char strings)
//
length += (rec.HostName.Length + rec.ApplicationName.Length +
rec.ServerName.Length + clientInterfaceName.Length +
rec.Language.Length + rec.Database.Length +
rec.AttachDbFilename.Length) * 2;
if (useFeatureExt)
{
length += 4;
}
// allocate memory for SSPI variables
// only add lengths of password and username if not using SSPI
var userName = "";
var encryptedPassword = new byte[0];
if (rec.UseSspi)
{
length += rec.ClientToken.Length;
}
else
{
userName = rec.UserName;
encryptedPassword = ObfuscatePassword(rec.Password);
length += userName.Length * 2 + encryptedPassword.Length;
}
var feOffset = length;
if (useFeatureExt)
{
if ((requestedFeatures & TdsEnums.FeatureExtension.SessionRecovery) != 0)
{
length += SessionRecoveryFeatureRequestLengths(recoverySessionData).totalLength;
}
if ((requestedFeatures & TdsEnums.FeatureExtension.GlobalTransactions) != 0)
{
length += 5;
}
length++; // for terminator
}
writer.NewPackage(TdsEnums.MT_LOGIN7);
writer.WriteInt32(length);
if (recoverySessionData == null)
{
writer.WriteInt32((TdsEnums.DENALI_MAJOR << 24) | (TdsEnums.DENALI_INCREMENT << 16) | TdsEnums.DENALI_MINOR);
}
else
{
writer.WriteUInt32(recoverySessionData.TdsVersion);
}
writer.WriteInt32(rec.PacketSize);
writer.WriteInt32(TdsEnums.CLIENT_PROG_VER);
writer.WriteInt32(GetCurrentProcessIdForTdsLoginOnly());
writer.WriteInt32(0); // connectionID is unused
// Log7Flags (DWORD)
var log7Flags = 0;
/*
* Current snapshot from TDS spec with the offsets added:
* 0) fByteOrder:1, // byte order of numeric data types on client
* 1) fCharSet:1, // character set on client
* 2) fFloat:2, // Type of floating point on client
* 4) fDumpLoad:1, // Dump/Load and BCP enable
* 5) fUseDb:1, // USE notification
* 6) fDatabase:1, // Initial database fatal flag
* 7) fSetLang:1, // SET LANGUAGE notification
* 8) fLanguage:1, // Initial language fatal flag
* 9) fODBC:1, // Set if client is ODBC driver
* 10) fTranBoundary:1, // Transaction boundary notification
* 11) fDelegatedSec:1, // Security with delegation is available
* 12) fUserType:3, // Type of user
* 15) fIntegratedSecurity:1, // Set if client is using integrated security
* 16) fSQLType:4, // Type of SQL sent from client
* 20) fOLEDB:1, // Set if client is OLEDB driver
* 21) fSpare1:3, // first bit used for read-only intent, rest unused
* 24) fResetPassword:1, // set if client wants to reset password
* 25) fNoNBCAndSparse:1, // set if client does not support NBC and Sparse column
* 26) fUserInstance:1, // This connection wants to connect to a SQL "user instance"
* 27) fUnknownCollationHandling:1, // This connection can handle unknown collation correctly.
* 28) fExtension:1 // Extensions are used
* 32 - total
*/
// first byte
log7Flags |= TdsEnums.USE_DB_ON << 5;
log7Flags |= TdsEnums.INIT_DB_FATAL << 6;
log7Flags |= TdsEnums.SET_LANG_ON << 7;
// second byte
log7Flags |= TdsEnums.INIT_LANG_FATAL << 8;
log7Flags |= TdsEnums.ODBC_ON << 9;
if (rec.UseReplication)
{
log7Flags |= TdsEnums.REPL_ON << 12;
}
if (rec.UseSspi)
{
log7Flags |= TdsEnums.SSPI_ON << 15;
}
// third byte
if (rec.ReadOnlyIntent)
{
log7Flags |= TdsEnums.READONLY_INTENT_ON << 21; // read-only intent flag is a first bit of fSpare1
}
// 4th one
if (rec.UserInstance)
{
log7Flags |= 1 << 26;
}
if (useFeatureExt)
{
log7Flags |= 1 << 28;
}
writer.WriteInt32(log7Flags);
writer.WriteInt32(0); // ClientTimeZone is not used
writer.WriteInt32(0); // LCID is unused by server
// Start writing offset and length of variable length portions
var offset = TdsEnums.YUKON_LOG_REC_FIXED_LEN;
// write offset/length pairs
// note that you must always set ibHostName since it indicates the beginning of the variable length section of the login record
writer.WriteInt16(offset); // host name offset
writer.WriteInt16(rec.HostName.Length);
offset += rec.HostName.Length * 2;
// Only send user/password over if not fSSPI... If both user/password and SSPI are in login
// rec, only SSPI is used. Confirmed same behavior as in luxor.
if (rec.UseSspi)
{
// case where user/password data is not used, send over zeros
writer.WriteInt16(0); // userName offset
writer.WriteInt16(0);
writer.WriteInt16(0); // password offset
writer.WriteInt16(0);
}
else
{
writer.WriteInt16(offset); // userName offset
writer.WriteInt16(userName.Length);
offset += userName.Length * 2;
// the encrypted password is a byte array - so length computations different than strings
writer.WriteInt16(offset); // password offset
writer.WriteInt16(encryptedPassword.Length / 2);
offset += encryptedPassword.Length;
}
writer.WriteInt16(offset); // app name offset
writer.WriteInt16(rec.ApplicationName.Length);
offset += rec.ApplicationName.Length * 2;
writer.WriteInt16(offset); // server name offset
writer.WriteInt16(rec.ServerName.Length);
offset += rec.ServerName.Length * 2;
writer.WriteInt16(offset);
if (useFeatureExt)
{
writer.WriteInt16(4); // length of ibFeatgureExtLong (which is a DWORD)
offset += 4;
}
else
{
writer.WriteInt16(0); // unused (was remote password ?)
}
writer.WriteInt16(offset); // client interface name offset
writer.WriteInt16(clientInterfaceName.Length);
offset += clientInterfaceName.Length * 2;
writer.WriteInt16(offset); // language name offset
writer.WriteInt16(rec.Language.Length);
offset += rec.Language.Length * 2;
writer.WriteInt16(offset); // database name offset
writer.WriteInt16(rec.Database.Length);
offset += rec.Database.Length * 2;
var nicAddress = GetNetworkPhysicalAddressForTdsLoginOnly();
writer.WriteByteArray(nicAddress);
writer.WriteInt16(offset); // ibSSPI offset
if (rec.UseSspi)
{
writer.WriteInt16(rec.ClientToken.Length);
offset += rec.ClientToken.Length;
}
else
{
writer.WriteInt16(0);
}
writer.WriteInt16(offset); // DB filename offset
writer.WriteInt16(rec.AttachDbFilename.Length);
offset += rec.AttachDbFilename.Length * 2;
writer.WriteInt16(offset); // reset password offset
writer.WriteInt16(0);
writer.WriteInt32(0); // reserved for chSSPI
// write variable length portion
writer.WriteUnicodeString(rec.HostName);
// if we are using SSPI, do not send over username/password, since we will use SSPI instead
// same behavior as Luxor
if (!rec.UseSspi)
{
writer.WriteUnicodeString(userName);
writer.WriteByteArray(encryptedPassword);
}
writer.WriteUnicodeString(rec.ApplicationName);
writer.WriteUnicodeString(rec.ServerName);
// write ibFeatureExtLong
if (useFeatureExt)
{
writer.WriteInt32(feOffset);
}
writer.WriteUnicodeString(clientInterfaceName);
writer.WriteUnicodeString(rec.Language);
writer.WriteUnicodeString(rec.Database);
// send over SSPI data if we are using SSPI
if (rec.UseSspi)
{
writer.WriteByteArray(rec.ClientToken);
}
writer.WriteUnicodeString(rec.AttachDbFilename);
if (useFeatureExt)
{
if ((requestedFeatures & TdsEnums.FeatureExtension.SessionRecovery) != 0)
{
WriteSessionRecoveryFeatureRequest(writer, recoverySessionData);
}
if ((requestedFeatures & TdsEnums.FeatureExtension.GlobalTransactions) != 0)
{
WriteGlobalTransactionsFeatureRequest(writer);
}
writer.WriteByte(0xFF); // terminator
}
}