I have tried searching and I dont seem to be able to find a way of getting this info easily.
within my organisation I need to start checking the connection attributes used by developers on our SQL servers. Is there a simple way of viewing the connection settings of each process regardless of how it is set and see the connection type. E.g Some applications will use ADO, ADO.net, OLEDB or ODBC connections and they may set connections in various ways including using t-sql commands like set isolation level or calling SQLSetConnectAttr. I would also like to know what type of .Net data object (e.g. reader, adapter) that is being used but not sure if that is even possible.
Ideally I would like a result set with the spid, connection type and a series of columns representing the different connection attributes containing the current value for that connection. We have both 2000 and 2005 servers.