Linked Server Security Issues
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
ADVERTISEMENT
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
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
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 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
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
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
Oct 14, 2005
Hello there I have trying to figure out for days how to enable FullTrust for my Reporting Services security extension.
View 9 Replies
View Related
Jul 31, 2007
Hi,
I have posted this issue for a week, haven't got any reply yet, I posted it again and desperately need your help.
The article http://msdn2.microsoft.com/en-us/library/ms365343.aspx says:
Model Item Security can be set for differnt security filters, but when I use SQL Server Management Studio to set Model Item Security, it seems "Permissions" property surpass "Model Item Security" property. -- My report server is using Custom Authentication.
For example, in "Permissions" property of the model, if I checked "Use these roles for each group or user account" without setting any user or group, no matter what users I added to "Model Item Security" with "Secure individual model items independently for this model" checked, NO one user can see the model on report manager and report builder;
in above situation, if I added "user1" and gave role such as "Browser" role to "user1" in "Permissions" property, if I checked "Secure individual model items independently for this model" in "Model Item Security" property, even I did NOT grant "user1" to root model and any entities under the model, the "user1" is able to access the model and all entities in report builder.
My question is on the same report model, how to set "AdminFilter" (empty security filter) for administrator permissions and set "GeneralFilter" (filtered on UserID) for general user based on their UserID?
The article also says:
"Security filters are always applied, even for users who have Content Manager or Administrator permissions to the model. To allow administrators or other users to see all rows of an entity on which row-level security is defined, you can create an empty security filter (which always returns True) and then use the filter to grant those users access to all the rows."
So I defined 2 filters "GeneralFilter" and "AdminFilter" for "Staff" entity for my report model "SSRSModel", I expect after I deployed the report model, the administrator users use report builder to build reports with all rows available, and the non-admin users can only see rows based on their UserID.
I can only get one result at a time but not both:
either the rows are filtered or not filtered at all, no matter how I set the "SecurityFilter" for the entity: I tried setting both "AdminFilter" and "GeneralFilter" for SecurityFilter at the same time, combination of "DefaultSecurityFilter" and "SecurityFilter", or one at a time.
Your help is highly appreciated!
Desperate developer
View 1 Replies
View Related
Apr 26, 2007
hi i want to know what is the differance between
Persist Security Info=False;Integrated Security=Yes;
View 1 Replies
View Related
Oct 18, 2015
Is there any possibility to schedule SQL job execution as Windows Security Group? I need to run powershell script through SQL job with one of this group member's permissions.Â
View 4 Replies
View Related
Jul 6, 2007
I have Sql Server Express installed on Vista (service pack 2)
I have Visual Studio 2005 with an application that I'm trying to access it with within a WCF service.
The login ID of the service is added to the database.
The database has remote access turned on.
The ID is granted access to all databases within the server.
The thread is being set with WindowsProvider and the services set their thread to WindowsProvider.
The dataserver is set with using Windows Authentication for security.
When I open my connection to the database, though, it reports the typically useless message that the connection is not allowed and that the server may not allow remote connections.
How to I get past this? I've done everything right.
View 1 Replies
View Related
Jun 18, 2007
I want to use an Active Directory security group that is a Distribution List for a new role assignment for an existing report. Can someone tell me if this is possible? I get an error each time I try:
The user or group name <DLName> is not recognized. (rsUnknownUserName)"
View 1 Replies
View Related
Jan 12, 2006
We have experienced an issue with back backup / restore of a database originating from SQL Server 2000 to SQL Server 2003.
We have the following setup:
SQL Server 2000
- DatabaseA
- asdfUser (SQL User)
- asdfUser is (dbowner) of DatabaseA
- DatabaseB
- asdfUser (SQL User)
- asdfUser is (dbowner) of DatabaseB
SQL Server 2005 Standard
-asdfUser is NOT Setup as a user yet.
-We restore DatabaseA and DatabaseB to the SQL Server 2005 Standard. The databases are restored with the security permissions of asdfUser being the DB Owner of DatabaseA and DatabaseB.
-We create a new SQL user named asdfUser on the SQL Server 2005 box. We then try to add the UserMapping of DBOWNER for the DatabaseA and DatabaseB. We receive an error message stating that the asdfUser already have permissions to the databases. We proceed with the user creation without those permissions.
-We proceed to the login properties of the asdfuser and view their UserMappings. The asdfUser does not have access to DatabaseA or DatabaseB. We then add the UserMapping of DBOWNER to both DatabaseA and DatabaseB. We Try to select OK and we receive an error message that states that the user already has those permissions.
-When we query the UserID's of the asdfUser that is in the database and the UserID of the asdfUser that is created, they are two different values.
I assume this is a bug... any word on a fix?
View 1 Replies
View Related
May 14, 2008
Hi folks,
I have created an EndPoint in SQL Server 2005 as per the code below.
CREATE ENDPOINT OSTC_LMS_Endpoint
AUTHORIZATION LMSEndPointUsers
STATE = STARTED
AS HTTP(
PATH = '/ostc_sql_endpoint',
AUTHENTICATION = (BASIC),
PORTS = (SSL),
SITE = 'OSTC-DEV-001'
)
FOR SOAP (
WEBMETHOD 'ostc_SQLSoapTester'
(name='OSTC_LMS_06.dbo.ostc_SQLSoapTester',
FORMAT = ROWSETS_ONLY,
SCHEMA=STANDARD),
WSDL = DEFAULT,
LOGIN_TYPE = MIXED,
SCHEMA = STANDARD,
DATABASE = 'OSTC_LMS_06',
NAMESPACE = 'http://tempUri.org/'
)
GO
USE master
GRANT CONNECT ON ENDPOINT::OSTC_LMS_Endpoint
TO [LMSEndPointUsers]
GO
USE master
GRANT CONNECT ON ENDPOINT::OSTC_LMS_Endpoint
TO [ostc-dev-001endPointUsers]
GO
-----------------------------------------------------------------------------------------------------------------
The SPROC being exposed as the webmethod: -
USE OSTC_LMS_06
IF EXISTS (SELECT name FROM sysobjects
WHERE name = 'ostc_SQLSoapTester' AND type = 'P')
DROP PROCEDURE ostc_SQLSoapTester
GO
CREATE PROCEDURE ostc_SQLSoapTester
@UsersId char(12)
AS
SET NOCOUNT ON
SELECT FIRST_NAME,FAMILY_NAME
FROM USERS
WHERE USERS_ID = @UsersId
GO
USE OSTC_LMS_06
GRANT EXECUTE ON ostc_SQLSoapTester
TO LMSEndPointUsers
GO
USE OSTC_LMS_06
GRANT EXECUTE ON ostc_SQLSoapTester
TO [ostc-dev-001endPointUsers]
GO
----------------------------------------------------------------------------------------------------------------
The computer in question is our dev server and is running as a workgroup machine with the following: -
Win Server 2003
SQL Server 2005
.net Framework 2.0
No firewalls or Proxies are in the way.
The computer has to be as a workgroup machine to reflect our live server.
The user LMSEndPointUsers is a SQL Server Login
The user ostc-dev-001endPointUsers is a machine login
We have employed the Security class as per the information given in the SQL Server documentation with the intention of using the WS-Security headers that apparently are to be used when trying to authenticate using a SQL Server login.
----------------------------------------------------------------------------------------------------------------
Code used to connect and try and reciev the dataset.
string sUserName = "userName";
string sPassword = "Password";
LMS_Endpoint.OSTC_LMS_Endpoint wsSQLTester = new LMS_Endpoint.OSTC_LMS_Endpoint();
SqlSoapHeader.Security sqlSec = new SqlSoapHeader.Security();
sqlSec.Username = sUserName;
sqlSec.Password = sPassword;
XmlWriter writer = XmlWriter.Create("Security.xml");
writer.WriteStartElement("security");
sqlSec.WriteXml(writer);
wsSQLTester.sqlSecurity = sqlSec;
DataSet dsMySet = wsSQLTester.ostc_SQLSoapTester("RH6915145507");
Basically authorization is denied 401.
Hope someone can help here as have tearing my hair out.
Thanks in advance
View 2 Replies
View Related
