Security Issue While Selecting From A Linked Server:
Jul 6, 2001
NT 4/SP6a/ SQL7/SP1
I have a job owned by UserA on ServerA that selects data from ServerB. UserA
also exists on ServerB with same ID and password. UserA is dbo on ServerA
for all the databases. UserA is also member of the "System Administrators"
server role on ServerA. The job works OK. But if I remove it from "System
Administrators" server role and run the job, I get the following error
message and the job step fails:
Remote access not allowed for Windows NT user activated by SETUSER.
[SQLSTATE 42000] (Error 7410)
UserA on ServerB [remote server] has ReadOnly access. ServerB is linked to
ServerA using "they will be mapped to" option and UserA.
Any ideas!!!
Thanks
Abdul Gill
View 1 Replies
ADVERTISEMENT
May 13, 2014
I've got an OLEDB DB2 linked server to a db2/AS400 instance and selecting from a table on the server has never caused problems before. One of the columns is a large text field. If I select all the columns but the large text field, it returns as normal, but including the large text field now, I get:
"Transport error: shared memory provider error: 0 - no process is on the other end of the pipe"
The largest entry in the text field is about 5k characters, and there don't appear to be any strange characters.
View 0 Replies
View Related
Jan 19, 2007
I have setup a linked server on the same computer but different instances of sql. When I call the sprocs an error occurs... The message is...
The OLE DB provider "SQLNCLI" for linked server LINKEDSQL does not contain the table ""product"."dbo"."AccountTable". The table either does not exist or the current user does not have persmissions on that table.
I've checked the table "AccountTable" and it does exist. The database exist also which is "product". I also configure the linked server's RPC to "true" and timeout to "200". The linked server is also configured to use a single username and password which exist on the instance of sql that the linked server is connecting. Also, I enabled the instances of sql to allow remote connections and use mixed authentications.
The mode of access is that different client pc will call a method created with .NET, which in turn, call the sprocs on the linked server. Should I add all the client pc that will be calling the method? Any help or advice please... Thanks
View 5 Replies
View Related
Apr 13, 2007
What is the practical difference between using "Not be made" and "Be made without using a security context"?
I've searched and it seems that if you are serious about NOT wanting anyone except a single login to be able to access the linked server that you ought to use "Not be made", however it seems that elsewhere it suggests that "Be made without using a security context" will accomplish the same thing.
Is that right?
If so, then what is the point?
Ex: I'm trying to apply the fewest settings on a sql server B linked to sql server A, but I only want login ZZZ to be able to access B through A, so I figure I ought use "Not be made", however reading various places it seems that "Be made without using a security context" is suggested to do the same thing.
This quote from -
http://www.microsoft.com/technet/prodtechnol/sql/2000/books/c08ppcsq.mspx
"Be Made Without Using A Security Context Blocks access to all logins not explicitly mapped to the linked server."
View 4 Replies
View Related
Apr 3, 2007
Hi
I have 2 sql servers server1 and server2. I need to setup a linked server from server1 to server2.
I want to limit the access for 1 user to be able to use this linked server from server1. I also want a login/password for the linked server connection to be setup for the access.
I am not clear about thedifferent security levels and not sure which one I can use
View 3 Replies
View Related
Oct 19, 2005
We are trying to link an SQL2K server (Running in mixed mode) to an SQL2K Server (Running in Windows only mode).
View 3 Replies
View Related
May 27, 2008
SQL Server 2005 SP2
Windows Server 2003
I am attempting to figure why, when using a domain account, that I am unable to run queries against a linked server using my own security context.
I create the linked server:
EXEC sp_addlinkedserver @server='TestLink', @srvproduct='', @provider='SQLNCLI', @datasrc='ServerB', @provstr='Integrated Security=SSPI;'
EXEC sp_addlinkedsrvlogin 'TestLink', 'TRUE'
When I run a query against the linked server (SELECT * FROM TestLink.master.sys.servers) using my domain account, I get the following error:
Msg 18456, Level 14, State 1, Line 1
Login failed for user 'NT AUTHORITYANONYMOUS LOGON'.
OLE DB provider "SQLNCLI" for linked server "TestLink" returned message "Invalid connection string attribute".
I followed the instructions in the article "SQL Linked Server Query failed with €œLogin failed for user €¦€?" on the SQL Protocols blog, but I still am getting nowhere.
I've run out of ideas, and really need some help trying to figure out what is going wrong.
Brandon
View 4 Replies
View Related
Sep 14, 2006
I€™ve been working on getting a linked server through SQL 2005 to work with VFP 9.
I get access denied for any and all security set ups on the linked server. I€™ve checked the folder-level security settings and see that the user I€™m logged in as, and have tried through security settings, and they seem to have access.
What other security settings should I be checking?
View 5 Replies
View Related
Sep 3, 2015
We are trying to track Linked servers configuration. Is it possible to query Linked server configuration like mappings, options, etc?
View 2 Replies
View Related
May 4, 2015
How to change the Security Properties of a Linked Server.
In tsql, I need to setup a linked server and want to set the Properties -> Security -> For a login not defined in the list above, connections will: -> Not be made But I cannot figure out how to set it to Not be made. I manually set it in the GUI, and the scripted the linked server and when I ran the script to create it was set to Be made using the login's current security context. The create script did not even set it correctly.
I have looked around and all that I have found is a column in sys.linked_logins, uses_self_credential that looks promising. But I cannot change the system catalog.
how i can set the "For a login not defined in the list above" security property in tsql?
View 4 Replies
View Related
Sep 17, 2015
Here are some details:
Domain = dm
Windows User = dmTestUser, member of sysadmin on SQLFL and SQLNY
SQL Server 2012 = SQLFL
SQL Server 2012 = SQLNY
Table = Product (exists in SQLNY and SQLFL)
SP = spGetProduct() (resides on SQLFL)
SQL Login = dbuser (on SQLNY, member of dbo on Product table)
I created a linked server on SQLFL pointing to SQLNY. Â The local server login to remote login mapping is as follows:
Local Login = dmTestUser
Remote Login = dbuser
with "Be made without security context" option selected. Run spGetProduct() to get Product records from SQLNY and populate Product table on SQLFL via the linked server.Here are tests I did:I connected to SQLFL using dmTestUser in SSMS. Â I manually executed the stored procedure spGetProduct() without any issues. Â Procedure gets Product records from SQLNY and populates the Product table on SQLFL. Â Everything is good.
I created a SQL Agent job called "Get NY Product" on SQLFL to execute spGetProduct(). Â I invoked this agent job and it executed successfully.However no data was loaded to the Product table on SQLFL. Â My stored procedure caught this error: Cannot initialize the data source object of OLE DB provider "SQLNCLI11" for linked server "SQLNY".
1. When sql agent job runs, which account does it use? Â Is the SQL Server Service or SQL Agent?I believe it's the sql agent.
2. What do I need to do to make this work?
View 4 Replies
View Related
Mar 16, 2007
I have this posted in the VFP section of the forums but the more I find out about the issue the more I think there is an SQL Server security issues.
What I am doing is trying to connect to a LinkedServer of my SQL Express 2005 database running on windows vista. I have the linked server set up to connect to a local FoxPro table using the VFPOLEDB provider. If I log in as the 'sa' account I am able to do my query. But if I log into the server using a trusted connection it doesn't work. I get the error [The OLE DB provider "VFPOLEDB" for linked server "sys" reported an error. The provider did not give any information about the error.]
So I went in and set the BUILTINUser login as the sysAdmin. I also made sure that all of the security settings in SQL server were set up the same between the trusted user and the 'sa' user.
My question is, What am I missing? Is there something that I need to set on windows or is there a setting in SQL server?
View 2 Replies
View Related
Jun 19, 2006
We are having some commercial applications that are running on sql servers at different sites, all with sql security. The software vendor wants to copy data into staging tables on our BI server. But our BI server only support Integrated security.
But how can i say, in the definition of a linked server (at the remote machines) to swicth security system ?
If it is possible i could enter a fixed domain-account in that users fields, but i suppose that this field was intended for SqlServer security.
View 3 Replies
View Related
Aug 3, 2015
We are using Microsoft jdbc driver 4.1 connecting to SQL 2012, which has a linked server to another SQL 2012 server.Will linked server calls work with kerberos authentication using Microsoft jdbc driver 4.1? connection string looks like this:
jdbc:sqlserver://SQL01;database= product_db; integrated Security= true;authenticationScheme=JavaKerberos..We have the linked server connection configured to use "Be made using the login's current security context"
Date      8/3/2015 4:19:56 PM
Log      SQL Server (Current - 8/3/2015 3:49:00 PM)
Source      Logon
Message
Login failed for user 'NT AUTHORITYANONYMOUS LOGON'. Reason: Could not find a login matching the name provided. [CLIENT: 10.196.21.4]
View 2 Replies
View Related
Sep 9, 2015
I have two SQL Server machines - RPTPROD and DATAPROD.
I wanted to create a linked server from RPTPROD pointing to DATAPROD using Windows Authentication. Â When I tried to create this linked server, I keep getting this error
"Login failed for user 'NT AUTHORITYANNOYMOUS LOGON' (Microsoft SQL Server,Error: 18456)". Â
On the "Security" page, I chose "Be made using the login's current security context". Â I'm sysadmin on both SQL Server machines. Â
View 3 Replies
View Related
Dec 31, 2006
Hello,
I am experiencing a head-scratcher of a problem when trying to use a Linked Server connection to query a remote SQL Server database from our SAP R/3 system. We have had this working just fine for some time, but after migrating to new hardware and upgrading OS, DBMS, and R/3, now we are running into problems.
The target database is a named instance on SQL Server 2000 SP3, Windows 2000 Server. The original source R/3 system was also on SQL Server 2000 (SP4), Windows 2000 Server. I had been using a Linked Server defined via SQL Enterprise Manager (actually defined when the source was on SQL Server 7), which called an alias defined with the Client Network Utility that pointed to the remote named instance. This alias and Linked Server worked great for several years.
Now we have migrated our R/3 system onto new hardware, running Windows Server 2003 SP1 and SQL Server 2005 SP1. I redefined the Linked Server on the new SQL 2005 installation, this time avoiding the alias and referencing the remote named instance directly, and it tests out just fine using queries from SQL Management Studio. It also tests fine with OSQL called from the R/3 server console, both when logged on as the application service account with a trusted connection, and with a SQL login as the schema owner. From outside of the application, I cannot make it fail. It works perfectly.
That all changes when I try to use the Linked Server within an SAP custom program (ABAP), however. The program crashes with a database interface error. The database error code is 15274, and the error text is "Access to the remote server is denied because the current security context is not trusted."
I have set the "trustworthy" property on the R/3 database, I have ensured the service account is a member of the sysadmin SQL role, I've even made it a member of the local Administrators group on both source and target servers, and I've done the same with the SQL Server service account (it uses a domain account). I have configured the Distributed Transaction Coordinator on the source (Win2003) system per Microsoft KB 839279 (this fixed problems with remote queries coming the other way from the SQL2000 system), and I've upgraded the system stored procedures on the target (SQL2000) system according to MS KB 906954. I also tried making the schema user a member of the sysadmin role, but that was disastrous, resulting in an instant R/3 crash (don't try this in production!), so I set it back the way it was (default).
What's really strange is no matter how I try this from outside the R/3 system, it works perfectly, but from within R/3 it does not. A search of SAP Notes, SDN forums, SAPFANS, Microsoft's KnowledgeBase, and MSDN Forums has not yielded quite the same problem (although that did lead me to learning about the "trustworthy" database property).
Any insight someone could offer on this thorny problem would be most appreciated.
Best regards,
Matt
View 12 Replies
View Related
Sep 28, 2015
I have a scenario where I want to make a linked server query and report using windows service account credential. I can able to do link query if I RDP into the Server where linked server established using the service account and run query successfully  but local client SSMS with my credential fails connecting linked server or querying. Looks to be a sql double hoping problem if so configuration each client domain account to enable delegation will be challenging as mentioned in the following articles instead service account only might work if possible.
View 3 Replies
View Related
Dec 8, 2013
Here is my Problem:
1. I have sql 2008 R2 running on my LocalHost.
2. Created Data Base [Customer].
3. Created Linked Server [CUSTOMERLINK] USING Microsoft Jet 4.0 to link to Drive F:Data which has DBF files in it.
4. Create dbo.Customer_Upload Table.
5. INSERT INTO [Customer].[dbo].[Customer_UpLoad]
([Name],[Email])
SELECT
NAME,EMAIL
FROM [CUSTOMERLINK]...[CUS]
All this works fine. I can even put it in to an After Insert Trigger on another table and it works.
My problem is that I need this to work in a scheduled job.
F:Data is just a folder with files in it.
This info is from a Restaurant POS system and I need to update it every night.
I have tried every which way to to setup the security issue as there isn't any login security on the folder and SqlServerAgent wants security.
View 4 Replies
View Related
Nov 9, 2007
From my desktop in Management Studio (MS) I can connect to server A and run a query like,
select top 1 * from serverB.mydatabase.dbo.mytable, and all seems to be working. If I leave and come back after a few minutes it no longer works and I get this error:
Msg 18452, Level 14, State 1, Line 1
Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection.
If I remote connect to server B and run the query it works and when I try it from my desktop MS it starts working again. Any ideas on why this is happening?
View 8 Replies
View Related
Jun 20, 2008
Hi
I've created a linked table connection to an external data source within sql server 2005.
The link looks OK, I can see all the tables etc, but I want to create a view on another database to some of the tables on the linked database.
How do I perform something as simple as a select statement as I can't seem to be able to select anything.
Is there something that i need to do first in order to be able to select fields from the linked table?
Thanks
View 3 Replies
View Related
Jun 1, 2007
Can someone tell me how to go about adding security to a field in a linked table so that only certain people can access and modify the information? Thank you in advance to anybody who can help me out here!
View 6 Replies
View Related
Dec 18, 2007
I have a report with a hidden parameter that defaults to the logged in user. In this way, a user can only see his own information. However, I now need to give a group of administrators access to change the value of the parameter (to see information for other people). What is the best/most appropriate way to accomplish this?
I thought I'd try the Linked Report, but do not see how I can set the default value for the User ID parameter to the value of the current logged in user in the Reports Manager. Is that possible?
I also tried creating a report with the parameter open but restrictive access, and then use that report as a subreport on a report where the parameter is closed. However, a general user cannot open the subreport in this case because he doesn't have access to the open report.
Sarah
View 3 Replies
View Related
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
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
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
Sep 20, 2007
I've got a big problem that I'm trying to figure out:
I have an address table out-of-which I am trying to select mailing addresses for companies UNLESS a mailing address doesn't exist; then I want to select the physical addresses for that company. If I get multiple mailing or physical addresses returned I only want the most recently edited out of those.
I don't need this for an individual ID select, I need it applied to every record from the table.
My address table has some columns that look like:
[AddressID] [int]
[LocationID] [int]
[Type] [nvarchar](10)
[Address] [varchar](50)
[City] [varchar](50)
[State] [char](2)
[Zip] [varchar](5)
[AddDate] [datetime]
[EditDate] [datetime]
AddressID is a primary-key non-null column to the address table and the LocationID is a foreign key value from a seperate Companies table.
So there will be multiple addresses to one LocationID, but each address will have it's own AddressID.
How can I do this efficiently with perfomance in mind???
Thank you in advance for any and all replies...
View 2 Replies
View Related
Jun 19, 2000
Hi:
Can anybody tell me the advantage and disadvantage to use NT security for SQL Server 7.0? For a corporation with 400 users, what is your recommendation for the SQL Server security management. Thanks.
Joan
View 1 Replies
View Related
Jan 21, 2008
I have three tables that are joined together to retrieve a piece of data.Table One has the columns pid1(PK), idTable Two has the columns pid1, qid1, idTable three has columns qid1, question, answerHere is my sql statement:SELECT Table1.pid1, Table2.qid1, Table3.Question FROM Table1 JOIN Table2 ON Table1.pid1=Table2.pid1 JOIN Table3 ON Table2.qid1=Table3.qid1 WHERE Table1.pid=...(N)I put an elipsis because I'm stuck on how to proceed from here. See I need to randomly select a value from column pid1 in table1 then use this value to replace N in the sql statement, then have the statement run and randomly return exactly one piece of data instead.Is this possible? How can you randomly get one piece of data from joining three table without a WHERE CLAUSE.
View 1 Replies
View Related
Mar 30, 2004
Hi There,
We have problems getting the right records from a joined table.
First we have a table with all our Customers (eg. [Algemeen-B])
We join them with a table representing all Contacts per Customer (eg. [Algemeen-C])
An SP joins the tables because we have a few dynamic filters and parameters.
What we need is to link a maximum of @amount contacts per Customer.
Where each contact has a RANK (the TOP @amount ) joining the Customer Table.
We come thisfar:
-- The SP results this SQL select:
select *
from [Algemeen-B] left outer join
(
select * from [Algemeen-C] AS C
where exists
(
select * from [Algemeen-C]
group by bedrnummer
having bedrnummer=C.bedrnummer And min(rank) <= C.Rank and count(*) <= 2
) ) S
on [Algemeen-B].bedrnummer = S.bedrnummer
where KWP>=8
---
In this case I should return a maximum of 2 contacts per each Distinct Customer.
But I think the: 'And min(rank) <= C.Rank and count(*) <= 2' is wrong.
How can I select the TOP 2 Ranked Contacts for each Customer?
Because when there are no Contacts for the Customer it should return only the Columns for [Algemeen-B]
and NULL values for the Colums for [Algemeen-C].
If you need more info Tell me..
Hope someone can help me out..
Colin
View 6 Replies
View Related
May 24, 2015
preparation of a query to get the data from xml which has xmlns in first few lines.
Here I am attaching the xml file(not able to attach ".xml" file, so converted it to ".txt" ).
View 9 Replies
View Related
Feb 26, 2006
Hi, I think you can do this in oracle but I'm trying to figure a way to do it in ms sql.. I need to select a distinct combination of columns, like so...
select distinct(ItemName,ItemData,FID) from tblSIFups
Does anyone know how to achieve that? I have multiple data where I shouldn't and I don't have any control over the application so I need to clean it this way.
thanks, nicki
View 1 Replies
View Related
Dec 4, 2014
I use from sql server 2008. and c#
what is the best connectionstring?
I don't know if i use Persist Security Info and Integrated Security or not?
And if yes then their value must be true or false?
View 1 Replies
View Related
Jul 22, 2015
I’m trying to extract a list of records that don’t appear between 2 dates. I have 4 tables:
tblCustomers
tblMachines
tblServiceOrders
tblMachinesServiced
tblCustomers contains a CustomerID that is unique to each customer.
tblMachines contains a list of all machines with a MachineID that is unique to each machine. It also contains the CustomerID number.
tblServiceOrders contains a list of each time each customer was serviced. It contains the ServiceDate, CustomerID, and ServiceOrderNo. But it does not have any information on the machines.
tblMachinesServiced contains a list of each machine that was serviced for each service order. It contains the ServiceOrderNo and the MachineID number.
What I want is to be able to extract a list of machines that were not serviced between 2 dates. What I end up getting is a list of machines that were serviced outside of the date range I provide.
For instance, say machine A was serviced in 2013 and 2015 but not in 2014. And say machine B was serviced in all 3 years. When I try to extract my list of machines not serviced in 2014 I end up with a list that contains machine A-2013, A-2015, B-2013 & B-2015. But what I need is just machine A-2014, since that machine wasn’t serviced in 2014.
I’ve tried several different queries but here is an example:
SELECT tblMachines.MachineID,ServiceMachines.ServiceDate
FROM tblMachines
LEFT JOIN
(SELECT MachineID, ServiceDate FROM tblServiceOrders, tblMachinesServiced
WHERE tblServiceOrders.ServiceOrderNo=tblMachinesServiced.ServiceOrderNo
) ServicedMachines
ON tblMachines.MachineID=ServicedMachines.MachineID
WHERE YEAR(ServiceDate) != '2014'
I understand why it returns the records that it does, but I'm not sure how to get what I want, which is a list of machines not serviced in 2014.
View 9 Replies
View Related
