Hi,

can anyone help me with WCF sql adapter error . I am getting login failed exception on the production from the SQL server when i am trying to insert table operation data through WCF SQL adapter.

Problem here is SQL table is running under different user on the dev and the Production and when I specify prod user ,I am always getting login failed exception from the SQL .

---------------------------------------------------------------------------------------------------------

Rohit

The error is quite clear. The credentials you are using don't have access on SQL server or don't have insert permissions on table. (It will help if you could post the Event Log error both from SQL server and BizTalk server).

To check that, try to login (and check if it has insert permissions) on prod SQL server management studio, using the credentials that your SQL adapter is using. Also, if you are not using credentials on port, then make sure that the host instance user have access on the SQL server.

Post your findings and we will help you resolve the issue.

• Edited by Abhipal Singh 17 hours 0 minutes ago Added more detail to answer

Hi Rohit,

This problem occurs because the user account that you used to access on production database or that tries to insert the records does not have sufficient permissions.

You need to grant permission to the user to access database and/or to insert records in tables.

Please follow below useful article,

https://sandroaspbiztalkblog.wordpress.com/2012/02/29/cannot-open-database-requested-by-the-login-the-login-failed-or-the-execute-permission-was-denied-on-the-object-database-schema-dbo/

And if your error is not related to above article then please share proper error details to understand your issue. 

Hi,

The sql user i am using in the production is the same user under which table is running .The table is not running under dbo. but "sqlReportingadmin.reportingData " were sqlReportingadmin is having sysadmin and db creator rights .

on the development machine the table is running under sqladmin.reportingData  were sqladmin is having Sysadmin and db creator rights .

All things works fine in the development environment but when I move to production and change the user credential to sqlReportingadmin and change the password ,Application does not work .

I am always getting login failed exception .

Note : Using SSMS i can login with same credential  without any issue

-------------------------------------------------------------------------------------------------------------

rohit

• Edited by RohitSahoo 16 hours 46 minutes ago

Have you checked any error message coming in Eventlog, Please share the error log to resolve the issue.

Have you checked if you have insert permissions on the table?

There is a little confusion, The way you wrote table names, it looks like you are talking about changing schema names. Where prod schema name is sqlReportingAdmin and table name is reportingData, it is so? This makes me ask another question, does the user have rights on schema?

Also, can you share the event log errors.

Its the same user under which table is running ,so user has all the rights as it is sysadmin .

Error is "Login failed for user domain/sqlReportingadmin " in both sql logs as well as biztalk .

In BizTalk Server, where have you specified this user?

Also, Stop/Start your send port to make the changes live. If you don't do that, BizTalk's send port will use old credentials.

Hi Rohit ,

Can you try doing certain changes in WCF SQL binding properties of the send port

Go to WCF SQLbinding Binding tab

This will TableOp/Insert/sysadmin/reportingData statements. You need to  Edit the table reference on the Operation. So change it from FROM sysadmin.reportingData to  sqlReportingadmin.reportingData .

Hope this will help .

Thanks

Abhishek

• Edited by Abhishek0127[Abhishek kumar]MVP 16 hours 6 minutes ago
• Marked as answer by RohitSahoo 15 hours 57 minutes ago

Thanks Abhishek0127 for quick solution , it worked .

I tried all ways but no where these changes are being mentioned .

----------------------------------------------------------------------------------------------------------------

rohit

Hi Rohit,

No issue .Another approach you can try is to use untyped schema instead of typed one and pass the required SQL statement through untyped message.

Thanks

Abhishek

• Marked as answer by RohitSahoo 15 hours 50 minutes ago

HOLD ON!

There's a bit more going on here.  It seems there's confusion between the database schema and database owner.  Simply changing schema in the Operation is not a viable solution as it does not actually fix the problem.  The schema is still embedded in the Xml schema.

The underlying problem is that your test and prod instances are apparently different schemas* and changing to untyped messages is not a good idea.

Can you change TEST/DEV so it matches PROD?  If not, a much better option is to create a Stored Procedure in the PROD schema that does the insert.

*The database schema in this case is somewhat analogous to a .Net namespace.  So, while Foo.MyClass and Bar.MyClass might have the exact implementation, they are not the same.

• Edited by Johns-305 [boatseller]MVP, Moderator 14 hours 0 minutes ago

The error is quite clear. The credentials you are using don't have access on SQL server or don't have insert permissions on table. (It will help if you could post the Event Log error both from SQL server and BizTalk server).

To check that, try to login (and check if it has insert permissions) on prod SQL server management studio, using the credentials that your SQL adapter is using. Also, if you are not using credentials on port, then make sure that the host instance user have access on the SQL server.

Post your findings and we will help you resolve the issue.

• Edited by Abhipal Singh Sunday, August 30, 2015 2:03 PM Added more detail to answer

Hi,

The sql user i am using in the production is the same user under which table is running .The table is not running under dbo. but "sqlReportingadmin.reportingData " were sqlReportingadmin is having sysadmin and db creator rights .

on the development machine the table is running under sqladmin.reportingData  were sqladmin is having Sysadmin and db creator rights .

All things works fine in the development environment but when I move to production and change the user credential to sqlReportingadmin and change the password ,Application does not work .

I am always getting login failed exception .

Note : Using SSMS i can login with same credential  without any issue

-------------------------------------------------------------------------------------------------------------

rohit

• Edited by RohitSahoo Sunday, August 30, 2015 2:17 PM

Hi Rohit ,

Can you try doing certain changes in WCF SQL binding properties of the send port

Go to WCF SQLbinding Binding tab

This will TableOp/Insert/sysadmin/reportingData statements. You need to  Edit the table reference on the Operation. So change it from FROM sysadmin.reportingData to  sqlReportingadmin.reportingData .

Hope this will help .

Thanks

Abhishek

• Edited by Abhishek0127[Abhishek kumar]MVP Sunday, August 30, 2015 2:58 PM
• Marked as answer by RohitSahoo Sunday, August 30, 2015 3:06 PM

Hi Rohit,

No issue .Another approach you can try is to use untyped schema instead of typed one and pass the required SQL statement through untyped message.

Thanks

Abhishek

• Marked as answer by RohitSahoo Sunday, August 30, 2015 3:13 PM

HOLD ON!

There's a bit more going on here.  It seems there's confusion between the database schema and database owner.  Simply changing schema in the Operation is not a viable solution as it does not actually fix the problem.  The schema is still embedded in the Xml schema.

The underlying problem is that your test and prod instances are apparently different schemas* and changing to untyped messages is not a good idea.

Can you change TEST/DEV so it matches PROD?  If not, a much better option is to create a Stored Procedure in the PROD schema that does the insert.

*The database schema in this case is somewhat analogous to a .Net namespace.  So, while Foo.MyClass and Bar.MyClass might have the exact implementation, they are not the same.

• Edited by Johns-305 [boatseller]MVP, Moderator Sunday, August 30, 2015 5:03 PM