0

I have a SQL Server 2017 database which contains some CLR stored procedures. When trying to call those CLR SP's, I get a "login failed" error back from the CLR SP.

My user is connected and works fine - it seems that the CLR SP is coded to run as another specific user.

I don't have access to the underlying code for the CLR SP's. Is there any way for me to change which user it's executing as?

Improve this question
5
  • 1) What is the stored procedure supposed to be doing? Something external, such as filesystem or network access? It could be using unsafe code to do a true windows login using credentials that have changed. 2) Is the T-SQL wrapper for the stored procedure using the EXECUTE AS 'some_user' option? **3)**When you say that your "user" is connected and working, what do you mean? Are you testing a different User (i.e. not a Login) via EXECUTE AS USER = 'username'; statement? Please update the question with the answers. Thanks.
    – Solomon Rutzky
    Commented Apr 4, 2020 at 6:14
  • @SolomonRutzky it's a CLR SP which exports data to CSV and appears to be trying to run as since-deleted SQL user - I was able to work around it with bcp but still curious if there's a way to fix this. It's not using EXECUTE AS, and lastly, I am referring to my Windows account which has sysadmin rights on the box and which I am using to execute the SQL SP which in turn calls the CLR SP.
    – blizz
    Commented Apr 4, 2020 at 6:18
  • Hi there. The first answer just restates the question which is why I asked for clarification. How about this: please add the exact and full error message to the question. SQLCLR methods can impersonate the caller's Windows Login, but that would never get a login failure since you logged into Windows with it. Else it uses the SQL Server service account login which also can't fail as SQL Server wouldn't be running. But that is for authentication to Windows. Now, if the SQLCLR code is connecting to SQL Server using a regular connection with a given login and password, that could fail.
    – Solomon Rutzky
    Commented Apr 4, 2020 at 13:36
  • 1
    It could be the proc has a hard-coded credentials or retrieves the credentials from an external source. If hard-coded, you'll need the source code to fix it.
    – Dan Guzman
    Commented Apr 4, 2020 at 13:37
  • 1
    If SSDT installed the CLR proc, it may have added the source files in sys.assembly_files. Otherwise export the assembly binary to a file and use ILSpy to decompile it. github.com/icsharpcode/ILSpy
    – David Browne - Microsoft
    Commented Apr 4, 2020 at 14:11

1 Answer 1

Reset to default
2

Assuming that the SQLCLR method truly is trying to log into SQL Server as a non-existing login ("user" is database-level), then that should only be due to using a regular / external connection (instead of the internal "context connection") and specifying a SQL Server login and password (instead of using a trusted connection / integrated security). { still, having the exact error message is always helpful :-) }

In terms of keeping the same process, you have two main options:

  1. Re-compile the code with a new login and/or password:
    1. There is a slight chance that the source code is in sys.assembly_files (it would be a row where file_id <> 1), but that practice was done (at least by default) in earlier versions of Visual Studio / SSDT and you don't really see it that much anymore.
    2. Assuming that the source code isn't in that system view, you would need to export the actual assembly (same view, but this time you do want WHERE [file_id] = 1) and then you can use a disassembler to get the source code.
  2. A much easier path should be to just re-add that deleted login. Is there a reason not to? If you aren't sure what password is being used, the two options noted above for getting the source code would also be how you find the password.
Improve this answer
1
  • This one did it. I was able to extract the old users password using the second method.
    – blizz
    Commented Apr 5, 2020 at 14:51

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.