Problems With Linked Server Setup Using Impersonated Option

Oct 26, 1999

I am having problems setting up a linked server in 7.0 Enterprise Manager. I am trying to setup the linked server using the "they will be imperonated" choice for the "For all users that are not defined in the list below" choice on the security tab. All servers I am trying to link to are registered in enterprise manager.

I can setup the linked servers on the source server itself and access tables from the linked servers as long as I am logged into the source server (where the linked servers are defined). E.G. I am logged into server SQL2 and have linked to server TST2. I can see tables from SQL2 in enterprise manager just fine.

However, when I try to access the linked server (TST2) from an enterprise manager running on an NT workstation (NT-RINEHARTS) or a 9x PC than I get the following error:

18456: Login failed for user ''.

It looks like the domain nameusername is not getting passed along to the linked server. Why won't this work from a remotely run Enterprise Manager? IS it supposed to? AM I leaving something out?

Again all linked servers are registered in Enetrprise Manager running on the NT workstation.

View 1 Replies


ADVERTISEMENT

Linked Server Option ?

May 11, 2004

Hi Everone,
I'm new here so please bear with me. I'm not sure how to ask this question so I have not done a search yet.
I have a proc that creates select statements on the fly from a feeder metadata table for ten tables at a time, all the time. Until now everything was on one server. Now some of the data tables are on another server, but this is not a problem.
There are some times when one of the tables is missing and the proc throws an error (as it should) and stops the execution of the 9 table builds. When it was all on one server, it would always throw an error and continue. Now when it's reading through a linked server it will not continue.
What can I do ? Thank you all.

Ken

View 4 Replies View Related

SQL 2012 :: Create Linked Server With Failover Partner Option?

Jul 22, 2014

it is possible to create Linked server with Failover partner option. I can query when primary server and getting the error when I set the DB Fail over. I have tried with following script and also gone through different sources, but failed. Please see the script and error below.

EXEC master.dbo.sp_addlinkedserver
@server = N'MIRRORLink',
@srvproduct=N'',
@provider=N'SQLOLEDB',
@provstr=N'Server=primary;FailoverPartner=mirror;network=dbmssocn;',

[code].....

View 2 Replies View Related

Linked Server Setup

Feb 13, 2008

I am trying to set up a liked server using the UI in Management studio. In the Object Explorer I can see the server i want to link to MyServer2 and I have connected to it. I goto to the server in which i want to create the link MyServer1 and click on Server Objects then Linked Servers, New Linked Server.

In the server name I enter the server I want to link to, MyServer1. I don't know what to enter or where after that? What else do i need to set up?

When i try this select i get the following error.

select * from MyServer1.MyDB.dbo.MyTable

Error:

Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "SQLNCLI" for linked server "MyServer1" reported an error. Authentication failed.
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "SQLNCLI" for linked server "MyServer1".

View 9 Replies View Related

Linked Server Setup - SQL 2005 RTM

Jan 21, 2006

<<cross posted from microsoft.public.sqlserver.server>>Environment: Windows 2003 Standard Edition SP1, SQL 2005 EE RTM (bothservers)I am getting the following messages when I run this query:select * from LINKEDSERVER.databasename.dbo.tablenameOLE DB provider "SQLNCLI" for linked server "LINKEDSERVER" returnedmessage "An error has occurred while establishing a connection to theserver. When connecting to SQL Server 2005, this failure may be causedby the fact that under the default settings SQL Server does not allowremote connections.".Msg 782, Level 16, State 1, Line 0SQL Network Interfaces: No credentials are available in the securitypackageOLE DB provider "SQLNCLI" for linked server "LINKEDSERVER" returnedmessage "Client unable to establish connection".Msg 782, Level 16, State 1, Line 0SQL Network Interfaces: No credentials are available in the securitypackageLINKEDSERVER is set up as a SQL Server linked server., with no loginmapping, and connections will be made using the login's currentsecurity context.Both servers allow local and remote connections via TCP/IP, and NamedPipes is disabled on both servers. Both servers are trusted fordelegation, and the service accounts for the MSSQLSERVER service onboth servers are also trusted for delegation.We are auditing successful and failed logins, and are not seeing anyactivity on the linked server.Any help would be appreciated.Randy

View 3 Replies View Related

Linked Server Setup Error

Nov 6, 2007

I'm trying to link my local SQL 2005 with a remote oracle database. I have downloaded the oracle 10g client. It seems the client installed a data source driver for me to use (Oracle in OraDb10g_home2). I created a new data source named 'Company' which I have successfully connecting using that driver.

The following is the script I'm using to create my linked server:


USE master

GO

sp_addlinkedserver

@server = 'Company',

@provider = 'OraOLEDB.Oracle',

@srvproduct = 'oracle',

@datasrc = 'Company'

GO

sp_addlinkedsrvlogin

@rmtsrvname = 'Company',

@useself = 'false',

@rmtuser = 'Company1',

@rmtpassword = 'MyPassword'

These both execute successfully.

Then when I go to make a select call:

SELECT * FROM OPENQUERY(Company, 'SELECT * FROM CAMPUS')

I get the following error:

Msg 7399, Level 16, State 1, Line 1

The OLE DB provider "OraOLEDB.Oracle" for linked server "Company" reported an error. The provider did not give any information about the error.

Msg 7303, Level 16, State 1, Line 1

Cannot initialize the data source object of OLE DB provider "OraOLEDB.Oracle" for linked server "Company".

I think my proplem may be that this provider does not match with my data source.... Any ideas?

View 2 Replies View Related

Linked Server Setup Question

Oct 1, 2007

I'm trying to add a linked server (sql 2005 9.00.1399.06) to a sql server 2005 instance (9.00.3042). I have chosen "login's current security context. All I can see are the system tables on the remote server. It seems to be using the "NT AUTHORITYANONYMOUS LOGON" account instead of my windows account because if I make it sysadmin I can see all of the databases on the server. Does anyone know why it is doing this and how to get it to use my windows account to make the connection?

View 15 Replies View Related

Linked Servers / Ansi_Nulls Option

Jul 10, 2001

Im having trouble accessing a linked/remote server from a powerbuilder app.

I can run the stored proc from QueryAnalyzer, but from the app I get the error message - 'heterogenous query must have ANSI_NULLS set option ON, set it and retry'.

Is the ANSI_NULLS option set for each db, or each server? And how can I check a DB or Server to see what the setting for ANSI_NULLS is? Does the local and remote/linked server/db need this option ON?

Any idea why this remote query works form QA but not from an application?

Thanks-
Scott

View 1 Replies View Related

How To Setup Linked Server Within Same SQL Server

Mar 29, 2004

Been wandering around the searches and cannot find a specific answer so hopefully someone can help me. Basic situation is I'm setting up an automatic transfering of data to a historical database that resides on the same SQL Server.

exec sp_dropserver 'linked1', 'droplogins'
exec sp_addlinkedserver 'linked1', 'SQL Server'
exec sp_setnetname 'linked1', <Databasename>
exec sp_addlinkedsrvlogin 'linked1', 'false', null, <user>, <password>

SET ANSI_NULLS ON
go
SET ANSI_WARNINGS ON
go
select * from openquery (linked1, 'select * from dbo.table')

I run this and it says it cannot find the instance....

View 7 Replies View Related

In SqlServer Management Studio Express, Server Type Option Is Greyed Out, Also Publication Option Missing

Apr 27, 2008

Hi everyone In my SqlServer Management Studio Express, on start up it shows the server type option, but greyed.So that value is fixed to database engine. ( I'm trying to work on an SqlServer Compact Edition database through the SSMStudiothat's why I'm trying to get this to change.)Besides, after I connect i go to the Object Explorer, expand the server node, and go to Replication.When i expand replication, i get the "Local Subscription" option, but nothng for Publication.( I want to work on Merge Replication, that's why I desparately need Publication to work)Am i missing something here? I did not install SqlServer separately, I only have what comes bundled with the Visual Studio  2005 Setup.

View 2 Replies View Related

How To Setup Linked Servers With SQL Managment Studio

Dec 29, 2007

Hi Everyone,
I am using SQL Server 2005 with SQL Management Studio.
What I am trying to do is move tables and stored procedures from one database on one server to another database which is on a different server. Essentially, I am trying to move tables and stored procedures from a database on my local computer to a database on our staging server I was told the best way to do this would be to use a linked server. I have been trying to set one up using managment studio but right clicking on server-object and choosing 'new ---> Linked Server' but I have not been able to successfully create the connection.
Can anyone out there explain me to me how to setup the linked server using SQL 2005 Management Studio?
Thanks! 
 

View 3 Replies View Related

Setup And Upgrade :: One Machine 6 IPs - How To Setup DNS For Server

Apr 24, 2015

I am running a number of SQL instances on my PC. Within the network, I have think server with various System Center components. For compatibility reasons, some features of System Center 2012 R2 had to be delegated to different SQL databases. My question is, because there is now more than one IP address on my system, and each instance of SQL is assigned to its own IP, is there a way to setup DNS and SQL so the namespace points to the desired IP address? For Instance:

MSSQL2008 instance is set to run on = 11.12.13.1
MSSQL2012 is set to run on = 11.12.13.2
IN DNS:
A Record: Mike-PC = 11.12.13.1
A Record: Mike-PC = 11.12.13.2

If I want to use MSSQL2008 by specifying Mike-PC as the DNS name, how would I do that with 100% accuracy? If there is another way to get the job done, I am more than willing to approach this differently.

View 3 Replies View Related

Linked Server ( Not Able To Access Any Tables Under LINKED SERVER From My DESKTOP Enterprise Manager

Mar 25, 2002

Hi ,
On my Desktop i registered Production Server in Enterprise Manager
on that Server if i go to SecurityLinked Servers
There is another Server is already mapped, when i am trying to see the Tables under that one of the
Linked Server i am getting the Error message saying that
"Error 17 SQL Server does not exist or access denied"

if i went to Production Server location and if i try to see the tables i am able to see properly, no problems
why i am not able to see from my Desk top
i am using the sa user while mapping the Production Server on my DESKTOP using (ENTERPRISE MANAGER)

And i check the Client Network Utility in the Alias using Named Pipe only, i changed to TCP/IP still same problem
What might the Problem how can i see the Tables in Linked Server from my DESKTOP

Thanks

View 5 Replies View Related

DB Engine :: How To Point Linked Server To Specific Database / Rename Linked Server

Apr 24, 2015

I am using Linked Server in SQL Server 2008R2 connecting to a couple of Linked Servers.

I was able to connect Linked Servers, but I cannot point to a specific database in a Linked Server, also, I cannot rename Linked Server's name.

How to point the linked server to a specific database? How to rename the Linked Server?

The following is the code that I am using right now:

USE [master]
GO
EXEC master.dbo.sp_addlinkedserver
    @server = N'Machine123Instance456',
    @srvproduct=N'SQL Server' ;
GO
EXEC sp_addlinkedsrvlogin 'Machine123Instance456', 'false', NULL, 'username', 'password'  

View 6 Replies View Related

Scripting Stored Procedure Add With Linked Server Reference When Linked Server Is Not Available

Jul 18, 2006

Is there a way to bypass the syntax checking when adding a stored procedure via a script?

I have a script that has a LINKed server reference (see below) .

INSERT
INTO ACTDMSLINKED.ACTDMS.DBO.COILS ..etc.

ACTDMSLINKED does not exist at the time I need to add the stored procedure that references it.

PLEASE to not tell me to add the LINK and then run the script. This is not an option in this scenerio.

Thanks,

Terry

View 4 Replies View Related

SQL 2012 :: Configuring Memory Per Query Option And Index Create Memory Option

Feb 10, 2015

So I started a new job recently and have noticed a few strange configurations. Typically I would never mess with min memory per query option and index create memory option configuration because i just haven't seen any need to. My typical thought is that if it isn't broke... They have been modified on every single server in my environment.

From Books Online:
• This option is an advanced option and should be changed only by an experienced database administrator or certified SQL Server technician.
• The index create memory option is self-configuring and usually works without requiring adjustment. However, if you experience difficulties creating indexes, consider increasing the value of this option from its run value.

View 3 Replies View Related

Is SQL Server An Option?

Jun 6, 2006

First off, I apologize for not knowing what I'm talking about and being long winded. I'm trying to determine if SQL Server Express is an option for a client of mine. Their needs are beginning to go beyond what I'm comfortable with in Access, so I'm looking into the option of upgrading to a SQL Server Express back-end with a VB front-end.

Access doesn't require any setup beyond "File, new", so I know nothing about the background work required to get a database running on a platform like SQL Server.

My first concern is what kind of network admin rights do I need to install & use SSE..

It's unlikely that her IT group will just hand us the keys to any of their servers, so all we really have available is what we can put on her network drives. Am I right in assuming that using a SQL Server database would involve more network privileges than just dropping a file on the network and pointing my front end app at it?

With that said, I think I remember seeing something on one of the MSDN pages about setting up a database to run off of a CD-Rom. Could I somehow use this capability to get what I need?

View 6 Replies View Related

Using The SQL Server Config Option

Apr 11, 2008

I'm using a package configuration to set the value of a user variable which I then use inside my package to build out a certain expression. I got this to work with the XML file option, but I can't get it to work using the SQL Server option.

So this is what Im doing.

(1) I copy pasted the package that works with the XML config option.
(2) In the new package, I deleted the existing XLM config and created a new one, this time choosing the SQL Server option. After I finish, I see that the dbo.SSIS_Configurations table gets created in my database with the value of my variable in there.
(3) I run the package and I see that it works. I'm not sure at this point if it is working because it's reading the value from the config table or from the BIDs environment where I initialized the value.
(4) Anyway, I then go and update the value of my variable in dbo.SSIS_Configurations.
(5) When I run the package again, it doesn't see the new value. What am I doing wrong?

View 22 Replies View Related

Insert Into [option Table] In Sql Server 7

Sep 13, 2001

Dear,

i got solve for my problem later.But I meet with new difficulty,my the following gives example script

------------------------------------------
CREATE TRIGGER Niru_data ON dbo.trans_r
FOR INSERT
AS
DECLARE
@Code_Month char(4),
@name_table char(10),
@noreg char(7),
@tr_date char(6),
@Rec_contain char(20)
SELECT
@Code_Month = substring(tr_date,1,4) from inserted
SELECT
@name_table = "trans_"+@code_Month
SELECT
@noreg = a.noreg,
@tr_date = a.tr_date,
@rec_contain = a.rec_contain
FROM
trans_r a INNER JOIN inserted b
ON
a.noreg=b.noreg
insert into @name_table values(@noreg,@tr_date,@isi)
GO
--------------------------------------------------
That's trigger not succesfull cause i try to insert table with variable declaration (@name_table).
some body can help me ????

Thank you before that

View 1 Replies View Related

How To Set Server Option Using T-Sql (&#34;user Options&#34;)

Jul 31, 2002

One of the configuration options that can be set for the server is called "user options". User options contains sub-options such as ansi_warnings, ansi_padding, ansi_nulls, arithabort, etc..

Using T-SQL, how are these set?

sp_configure 'user options', @configvalue = 'value'
go
reconfigue with override
go

i.e. Are the individual option values summed. Say I wanted to set ansi_warnings (whose value is 8) and ansi_nulls (whose value is 32) on and all other user options off. Then is my @configvalue 40?

Sorry, but I don't feel BOL is clear on how to set these options.

TIA Gary

View 5 Replies View Related

How Use The Show Server Trace Option.

May 26, 2008

Sir

How to used Show Server Trace option.
Where I got this option in SQL server .
Pls Help me .

Yaman

View 3 Replies View Related

SQL Server Not An Option As A Data Source

Jan 17, 2007

I'm in VS 2005 trying to access a database running on MS Sql Server Express 2005 (local machine). In VS i choose Data | Connect to Database | and then i select change on the Add Connection Dialogue box. For some reason I'm only given two options which are the following:

MS Access Database File or MS SQL Server Database file. I've seen options in books showing MS SQL Server but that doesn't show as one of my options. I'm running the Server express locally and can access it through my Management Studio Express as localmachinesqlexpress.

Is this a limitation of Express or do i need to adjust some options in the IDE?

Any help would be great.

jeremy

View 2 Replies View Related

MDAC 2.8 Fatal Setup Error. This Setup Does Not Support Installing On This Operating System

Jan 5, 2006

I have XP Pro SP2 with MDAC 2.8.1022.  It had a problem so I tried to reinstall MDAC and got a Fatal Setup Error. This setup does not support installing on this operating system. I downloaded MDAC 2.8 1177 and get the same error.

I thought of uninstalling/reinstalling SP2, but this is a 2 month old Dell Latitude 610 with factory installed XP.  There is no Windows Service Pack 2 option listed in the Control Panel > Add/Remove Programs. 

There's some other strange things, so I wonder if they are related. 

1) I have Paul set up as an administrator account.  Some folders like MSSQL show that account with no permissions.  I grant all the permissions to Paul for that folder.  I come back later and the permissions are gone.

2) I deleted 20 files in Explorer, but 7 of them did not go away. I deleted those 7 again and they instatnly reappeared.  I deleted those 7 again and then they finally went away.

3) I get a slow reaction time for things like Windows Explorer and opening and closing programs. This is suprising since it has 2 gig of RAM and 2.3 Gig processor. Could it be a memory handling problem that's causing OS problems. Probably, the memory didn't handle the OS installation well and the whole system is compromised now.

 

View 12 Replies View Related

Setup And Upgrade :: 2008 R2 Setup UI Has Incomplete List Of Installed Features - Can't Uninstall RS

Jul 23, 2015

We're doing upgrades from SQL 2008 R2 to SQL 2014. This is blocked due to RS is installed but not configured. Our desired action is to uninstall RS and proceed with the upgrade. But when setuparp.exe is raised, it does not list all the features on the 'Select Features' page.  In fact, it only lists the last 2 shared features (SQ Client Connectivity SDK and Microsoft Sync Framework). However, all items appear to be listed on the 'Select Instance' page including RS. I've seen this issue on 2 of our SQL 2008 R2 Servers already.

View 3 Replies View Related

NO -- SQL Server Performance Condition Alert -- Option

Feb 4, 2004

I'm pretty new to MSSQL. We have SQL 2K and I was wondering if anyone has seen this issue before.

When I create/modify an Alert there is no option for "SQL Server Performance Condition Alert" in the Type under General tab.

Has anyone seen this before? What can I do to get this type of Alerts?

Just a note -- on my other SQL servers they have the options.

thanks in advance !

View 5 Replies View Related

SQL Server 2000 Password Retry Option

May 26, 2006

In SQL Server 2000, can you enable an option to check password retries? For example if an SQL Server ID logs incorrectly 5 times, you disable the login account.

Any help would be greatly appreciated. Thanks.

View 7 Replies View Related

T-SQL (SS2K8) :: New Query Option Is Not Visible In Server?

Mar 20, 2015

I just inherited a few Servers that have SQL Server 2000.

I cant find the new query Icon?

[URL]

View 2 Replies View Related

How To Change Server Option For Isolaiton Level?

Jul 20, 2005

I'm having problem with locking in update and select.my update takes long and select from clients happens all the time.I know with NOLOCK in select or set transaction isolation level, I canread data uncommited.I want to know if there is an option I can change on server ordatabaseto change isolation level for all incoming connections.thanks for your help in advance.

View 6 Replies View Related

SQL Server Database Services Option Greyed Out

Jul 15, 2007

Hi,



I'm trying to install SQL Server 2005 onto my XP Professional PC. When I get to the "Components to install" part, the "SQL server database services option" is greyed out. Infact, all options except "Workstation components..." are greyed out. I've tried uninstalling all previous versions of SQL server and trying again, but with the same results.



any help would be much appreciated

Simon

View 5 Replies View Related

Multiple Cascade Option In SQL Server 2005

May 26, 2008



Hi All,
Use Case1:
Just wanted to know if there is something like multiple cascade delete option in SqlServer 2005.
My table structure is similar to what is given below

create table test ( id int PRIMARY KEY, tname nvarchar(55))

create table childTest ( id1 int REFERENCES test(id) on delete cascade,
id2 int REFERENCES test(id) on delete cascade, tname nvarchar(55))......

The above creation fails for childTest since both id1 and id2 reference test with ondelete cascade.

As per Books online: The series of cascading referential actions triggered by a single DELETE or UPDATE must form a tree containing no circular references. No table can appear more than once in the list of all cascading referential actions that result from the DELETE or UPDATE.

Doesn't look like a circular reference here. Wanted to confirm if it is because test is being referenced twice in the childtable.

My requirement is if a row is deleted from test, it should delete matching rows from childTest(id1 and id2). Since delete cascade is not being allowed for both columns , I have to handle the delete in id2.

Use Case2:
Also can I have INSTEAD OF DELETE and Cascade delete on the same table?
I have a cascade delete from table1 to table2. which means when any row from table1 is deleted corresponding rows from table2 are deleted. and on table2 I would like to have an instead of delete where I would like to delete rows from another table say table3.

Thanks & regards
Sunil

View 4 Replies View Related

Setup And Upgrade :: Error Setup Account Privileges

Nov 15, 2015

I'm trying to install SQL Server Management studio 2012 on my Windows 7 (x64) standalone laptop.  When I click "New SQL stand-alone installation..." it runs a Setup Support Rules check and always fails "Setup Account Privileges". I've looked into the error and I keep getting that I need to change security rules but I don't have that option in window 7.  How do I get around this without having to resort to a computer running Windows Server?

I have Visual Studio 2013 premium installed along with Localdb v11.  I just want to connect and manage my database engine through SSMS when developing any application.

View 2 Replies View Related

Please Post Setup And Installation Questions In The Setup Forum

Nov 17, 2005

This forum is intended for users who are new to SQL Server, and have basic usage questions. If you have setup or installation issues or questions, you should check out the Setup forum.

Thanks

View 11 Replies View Related

Integrated Security Problems With Custom Server Option

Mar 13, 2008

Ok, I need to test my webservice with the 'use custom server' option on so that I can specify a base URL that I can reference from apps on a remote machine. I have a connection string I am using to access a sql server db that has Integrated Security=true and windows login has a login for it created in the db.

However, whenever I try to call a webmethod I get this error:
System.Data.SqlClient.SqlException: Login failed for user ''. The user is not associated with a trusted SQL Server connection.

But when I call the same webmethod specifiying a sql login / pw, and not integrated security, my webmethod works.

I want to use integrated security though, please help!

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved