Access Denied from Remote Server | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Access Denied from Remote Server

Hi Folks,
I have linked server configured of MYSQL databases using ODBC Driver with SQL Server database. A front end application is connected with this SQL Server database. Link server is working fine within same database as tested using QA. The password using for ODBC has admin rights in MYSQL server for all the databases. When I run query from Remote Server OR from front end application, it’s give error as follow. Application user have db_owner rights in the database. But still giving the following error.

Access to the remote server is denied because no login-mapping exists.
Then I have change the database user rights to Sysadmin and it’s work fine. But point is that this is not acceptable as it highly risky to use sysadmin rights within (even db_owner) application. so I need your advice how should I remove sysadmin rights and work out the application.
seems to me that SQL server database user (that use to access through application) do not enough access using link server to access mysql database. By the way I am using ODBC driver as Provider and "Be made without using a security context" option in security tab of Linked Server properties. Thanks in advance

You keep saying "MYSQL" – you are aware that there is a database platform called MySQL, which is not the same as MS SQL Server?

Talking about MYSQL in MSSQL is like speaking spanish in Greece [;)].
Linked Server properties, Security tab, make sure you have the correct settings. Check whether you have the correct access on it and also run:
select * from server.database.table (4 part name)

]]>

Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |