Adding AD Security group to a SQL linked server
Hello, I have a question about adding permissions for a user group on a linked server in SQL Server. I am not a security specialist, so I appeal to everyone. Here is the topo: I have private software that I'll call X installed on a Hyper-V virtual server. Users of this software connect through virtual machines using the Remote desktop. The application has its own SQL Server database instance. The security for this database is Windows Authenticated. Application X users all belong to a user group in the Windows Server 2019 Active Directory (AD). On the other hand, I have a Y application (Microsoft Dynamic 365) that is installed locally on each workstation and which has its own SQL instance. The application uses an SQL database which I would also call Y. As I would like to share information between the 2 SQL instances, I created a linked server on the X instance to the Y instance. I succeeded with some SQL query to insert, update, and delete some information in a database of instance Y from my instance X. To do this, I granted myself DB_owner rights on the database of X. I have reached the stage of allowing user group X to be able to run from application X using the same queries that interact on SQL server Y. However, I realize that I need to create each user of X in the SQL instance of application Y from the Security, Connection menu. My requests work when I grant DB_Owner roles. I would have thought that I could have used the same group used in AD for my X application. I tried well but it doesn't work. I don't know if this is due to the configuration of my linked server. Here is the configuration I am using: Could someone point me to a solution to avoid having to recreate every user of my AD from X in my instance Y and just link my group X.
SQL CLR questions for SQL 2014
Hello all, We have an active project in SQL 2014 Standard where SQL CLR was used and there is a need to add an assembly that has a COM dll reference. Can I please get some feedback on this regarding experiences and what concerns there are for performance and other issues? Another thought is to use WCF to loosely couple but again I would ask for feedback regarding this. Thanks in advance for any thoughts and ideas surrounding this. Bob
SQL Server Unit Test Project using Visual Studio
Hello All, I have created SQL Unit Test project using the built-in Visual Studio Test project. I am able to create test cases for all scenarios using Pre-Test, Test and Post-Test conditions. Also, I am able to create the Database in run-time using DACPAC. All these are fine till now. The problem I am facing is, I am not able to drop the database post execution of all my test cases. I have tried the following approaches where it failed to drop the database: 1. Custom scripts to forcefully drop the database in the finally block of the last test case 2. Order the test cases and in the Post-Test script of the last test case, added the drop script Is there a way that this can be achieved? All I need in short is - Drop the database at the end of my test execution of the last test case. Please can you help me with this or point me in the right direction? Thanks Aravind S
