January 30, 2015 at 4:52 pm
I have a stored procedure called from a website application that fails, but if I run the stored procedure in a query window, or a scheduled job runs it, it works fine.
The problem seems to be the linked server step that is writing to a MySQL database.
Is there a permission or ODBC setting I might be missing so that a web application with a SQL login can use the ODBC connection ??
I trapped the errors in a TRY-CATCH block:
What permission might a web based, SQL login need ??
ERROR_NUMBER() = 7303
ERROR_SEVERITY(), = 16
ERROR_STATE() = 1
ERROR_PROCEDURE() = My_SP_Name
ERROR_LINE() = 90
ERROR_MESSAGE() = Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "MYSQL_WEB_PROD".
I see some 2008 references to a folder needing permissions, but I don't have the same files on my 2005 server.
The stored procedure loads some variables from MSSQL tables, then inserts the variables into the MySQL table, so this is going from MSSQL to MySQL. Snippet:
set @cmd = ' INSERT INTO web_prod.wp_users
(id, user_login, user_pass, user_nicename, user_email, user_url, user_registered, user_activation_key, user_status, display_name)
VALUES (''' + @id + ''' , ''' + @Email1 + ''' , ''' + @userpass + ''' , ''' + @mem_nickname + ''' , ''' +
@Email1 + ''' , ''' + @url + ''' , ''' + @getdate-2 + ''' , ''' +
@SubDir + ''' , ''' + @ID + ''' , ''' + @mem_Name +''')'
EXECUTE (@cmd) AT MYSQL_WEB_PROD
Properties of the MSDASQL provider ?
February 2, 2015 at 11:23 am
Does anyone have any ideas of how I can troubleshoot this ?
Is there some other error trapping I can do to get a better explanation or more useful error messages ?? ??
The linked server itself seems fine, but doesn't want to work from a stored procedure invoked by a web application.
February 2, 2015 at 12:11 pm
Are you calling the linked server the same way directly and through the SP? Meaning same security context? Check the SP doesn't have any execute AS.
Linked servers get tricky because they can proxy credentials.
February 2, 2015 at 12:28 pm
I can run it from a scheduled job without problem, and I can run it manually from a query window without a problem. But when the web application is running the parent stored procedure, and then does an execute to the MySQL insert stored procedure, the the actual insert to MySQL fails.
All 3 methods are calling the same SP:
exec usp_MySQL_User_Insert @MemberID, @Password
The code in my first post above is coming from "[font="Courier New"]usp_MySQL_User_Insert[/font]"
February 10, 2015 at 9:33 am
That suggests to me that it's security somehow. The linked server can be set to use multiple security contexts, different than you in SSMS or SQL Agent. I'd look there.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply