how to grant access to a sql authentication account to access linked server

how to grant access to a sql authentication account to access linked server?

Thanks

-------------Problems Reply------------

you should go through this http://msdn.microsoft.com/en-us/library/ms190479.aspx

The following example creates a linked server S1_instance1 on an instance of SQL Server by using the SQL Server Native Client OLE DB provider.

EXEC sp_addlinkedserver
@server=N'S1_instance1',
@srvproduct=N'',
@provider=N'SQLNCLI',
@datasrc=N'S1\instance1';

This will depend on the security context you have set up in your linked server. If you have it set to...

  • Use the current security context: you need to ensure that the sql authenticated account is created on BOTH servers with the same username and password
  • Use a specific security context: you need to map the local sql authenticated account to the remote authenticated account on the linked server setup page

IMHO the first option is the simpliset method which we have used to good effect in the past but it does require you have admin permissions on both servers to set up the accounts

Category:sql server Views:0 Time:2011-03-22

Related post

  • Cannot log in using SQL authentication ONLY from a remote server 2011-12-09

    I am trying to connect to SQL Server 2008 from a remote server using 'sa' username and its password (I can log in normally with this username and password from my own computer - so "SQL Server and Windows authentication mode" is chosen). In the SQL S

  • Query access database from SQL management studio without using linked servers 2010-04-06

    How do I query a MS Access database directly from SQL Management Studio, without using a linked server? Ie. something like SELECT * FROM ["C:\Data\Accessdb.mdb"].[SomeTableInAccessDB] Obviously this won't work but is there a away to specify the acces

  • Problem with SQL subquery using Top() on Linked Server 2009-09-10

    I am using SQL Server 2008 and I have the following SQL script: Select o.CustomerId as CustomerNoId, OrderValue, OrderDate From dbo.Orders as o Inner Join ( Select Top (10) CustomerId From dbo.Customers where Age < 60 ) As c On c.CustomerId = o.Cu

  • Securing a SQL database account for an ASP.Net site 2009-09-18

    I am trying to secure a MSSQL database for our .Net website. I wanted to separate the database into different schemas so that we had an schema like 'Account' which had personal information and a schemas like 'Public' that had generic public content d

  • Access 2010 Project linked server db2 2014-09-19

    I've completed the link table wizard (http://office.microsoft.com/en-us/access-help/link-tables-in-an-access-project-by-using-the-link-table-wizard-adp-HP003089371.aspx) to create an OLE DB2 connection in an Access 2010 Project. In Access when the wi

  • SQL Server: Is there an "IF EXISTS" test for a linked server? 2010-06-23

    I want to be able to programmatically (in T-SQL) check if a specific linked server already exists for my current server and database (so that if the link doesn't exist yet, I can create it). I tried stuff like this: IF OBJECT_ID('myserver\devdb_1') I

  • Execute sybase stored procedure as linked server procedure sql server 2008 2011-02-22

    EDIT The final goal is to call a stored procedure hosted in sybase with input and output parameters from SQL Server 2008 via Linked Server I think title is pretty clear. My goal is to execute a stored procedure hosted in Sybase SQL Anywhere 8 in SQL

  • Entity Framework 4.1 code first with a DB2 Linked Server in SQL 2011-08-31

    I would like to use Entity Framework 4.1 Code-First to build connections to my databases whether they are on our SQL server or DB2. I feel like I'm almost there, but just can't get it to work. We're using SQL 2005 and have a Linked Server setup to ou

  • How integrate active directory and sql azure for authenticating users to access the application running on azure 2012-04-16

    I want to integrate on-premise active directory and sql azure for authenticating users to access the application running on azure. some users are in active directory and some in sql azure database. What i require is , when user access the application

  • What is the sql connection string I need to use to access localhost\SQLEXPRESS with Windows Authentication or SQL Authentication? 2011-03-12

    've installed SQL Express on my PC hoping to do some practice creating tables and then modifying them. I coded a webpage in Visual Studio to, basically, SELECT * from a table in the SQLEXPRESS, but I can never get the connection string to work. Pleas

  • sql authentication or Windows auth 2009-02-05

    For my asp.net website with forms authentication, I will use Windows integrated security to access a sql database. I will give DB permissions to the ASPNET or NETWORK SERVICE. Under what circumstances would I use SQL authentication instead? ---------

  • Security model (deployment) for MS Access application with SQL Server Backend 2010-01-18

    We have an application, consisting of an MS Access frontend (2007, mdb format), a few .net libraries and an SQL Server (2008) backend. I am working on an installer, which automatically installs the MS Access Runtime, our application, our libraries, S

  • Building an auditing system; MS Access frontend on SQL Server backend 2008-08-15

    So basically I'm building an app for my company and it NEEDS to be built using MS Access and it needs to be built on SQL Server. I've drawn up most of the plans but am having a hard time figuring out a way to handle the auditing system. Since it is b

  • handling the connection string with SQL Authentication 2009-01-17

    I am deploying a Windows Application that uses SQL Server 2005. The program will use SQL Authentication. My question is, how do you handle the connection string when you don't know what the username/password will be? Do you load this from an encrypte

  • How to Suppress SQL Authentication 2009-04-30

    I am really stumped on this, I am not sure if someone can at least point me in the right direction? I have an MS Access Reporting Application. It has a few local tables in it but predominantly relies on tables from a SQL Server 2005 database that it

  • 403 - Access is denied after authenticating 2009-08-14

    Greetings! When I try authenticating against my existing db I'm getting authenticated but I get the 403 page. If I just tried a wrong password I get 'wrong credentials' message as expected. I tried authenticating per sample app included with SpringSe

  • All MS Access queries to SQL file 2009-09-14

    I have a MS Access database with over 10 queries which need to be updated weekly. It is a real pain to export each one separately and then upload each one separately to the SQL on my server. I have tried to connect my Access database directly to my S

  • linked-server sql - access Problem 2009-10-06

    I have a SQL server 2000 and an Access database mdb connected by Linked server on the other hand I have a program in c # that updates data in a SQL table (Users) based data base access. When running my program returns the following error message: OLE

  • can't query SQL linked server -> MS Access remotely, only works on server 2010-01-14

    I'm working on a C# application that queries a SQL Server 2008 Express instance on a remote server (LAN). When I am at my workstation/dev machine, I can open SQL Server Management Studio and connect to the SQL instance through Windows Authentication,

Copyright (C) dskims.com, All Rights Reserved.

processed in 0.145 (s). 11 q(s)