How To Change Security Properties Of Linked Server
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?
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.
I'm running SQL Server 2005 Express SP1 on a Danish PC, and I need to have the server language set to Danish.
However, when I want to change the language in server properties (the current language is English (US) using Management Studio Express, I can't - all of the properties on the General page are greyed out.
I know that the SQL Server Express SP1 download is available in several languages (Danish not being one of them) - does that mean that I can't get my SQL Server Express SP1 installation to "speak" Danish at all?
In SQL 2005, the linked servers are not automatically enabled for RPC / RPC OUT like they were in SQL 2000. I have set the properties for my linked server by using SQL Management Studio.
I right click on the Linked Server --> Properties --> Server Options
There are options like: Collation Compatible, Data Access, RPC, RPC OUT, Use Remote Collation
There is a drop down box to set each value to TRUE or FALSE. Setting them through management studio works great, however for the production environment I need to set these properties programmatically. Is there a stored procedure or registry setting that would allow my program to set these values for a given linked server?
Hi,When I click on the properties of a linked server, all the Generalproperties are read - only, which means that if I want to edit anygeneral properties I have to delete the linked server and recreate it.Needless to say this is a pain. Is this by design or some kind of bug?Is there a workaround?Thanks
I have connected to the SQL Server 2005 instance usign the SQL Server Management Studio; I have changed the default locations for the database and log files. I have also rebooted the machine. When I look in the SQL Server Configuration Manager in the properties for the SQL Server (MSSQLSERVER) I see that the data path is still set to the old value and this field is read-only. How can this can changed without going through the registry?
I'm logged in as 'sa' and I'm using Microsoft Sql Server Mgmt Studio. I've established a Linked Server using Provider MSDASQL, and I'm able to retrieve data from the linked server. However, when I attempt to set the properties for my linked server, I get the following error message.
Adhoc updates to system catalogs are not allowed. (Microsoft SQL Server, Error: 259)
I'm not directly accessing the system catalogs (as far as I can tell), I'm actually using the Linked Server Properties user dialog to change the Server Options. Any help is appreciated.
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
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."
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
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.
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.
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?
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?
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?
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]
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. Â
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.
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.
Hello, I have created one application in visual studio 2005 and also created setup project of that application. now i want to install SQL Server Express edition with my application. so i have checked SQL Server Express 2005 in Setup Project Properities(Prerequisites...). now i want to change SQL Secirty Mode during setup. and i don't know how can we do this?
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.
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?
How can I programmatically change the properties of Send Mail task using Script Task. I want to change the From, To and Attachment parameters of the Send Mail task.
If the Script Task can't do it, Is there any alternative to do?
I store files in db in sql server 2008 by filestream. But when a column would be added to table which have filestream, properties of table would be changed. by every things change on table, retrieve files will faced to error. but store process work probably.
and filestream filegroup at following address will be empty. why?
Right click on table --> properties --> storage --> filestream filegroup
We are wanting to change the SA password on one of our Sql server 2012. My question is what are the implications? Will our agent jobs still run? SSIS packages still run under agent jobs? Is there a proper way/how to change SA password?
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!
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.
I am now checking with our IT if the SMTP corporate server has the correct relay authorisation for the new server. I may have overlooked this issue.
Hello,
Om my previous report server, I had not set amy DefaulthostName nor PermittedHost.
This allowed us to create subscriptions that get sent to our business partners with a long ever changing lists of email addresses and domains.
All server are build 2175, they ahve the same rsreportserver.config setting for the email portion. Problem is that on 2 of the servers, the email with a domain other than our internal domain do not get sent with error "The email address of one or more recipients is not valid". It works on the third server.
I tested our SMPT server from another application and it does send emails to external domains so the problem is really on the server side.
It is vey important because subscriptions are managed by a few business analysts, they spend a lot of time creating lot of subscriptions and the domains change often.
I tried on the dev server to define cox.net as Permitted host and send myself a report to my private email, it also returned the error.
Any idea as where to look at? is ther a specific CDO setting to allow relay?
ReportingServicesService!emailextension!4!01/27/2007-11:47:12:: Error sending mail, CDO error -2147220977, will not resend ReportingServicesService!emailextension!4!01/27/2007-11:47:12:: Error sending email. System.Runtime.InteropServices.COMException (0x8004020F): The server rejected one or more recipient addresses. The server response was: 550 5.7.1 <me@cox.net>... Relaying denied. IP name lookup failed [10.100.15.87]
at ReportingServicesCDOInterop.MessageClass.Send() at Microsoft.ReportingServices.EmailDeliveryProvider.EmailProvider.Deliver(Notification notification)
In our lab we have 2 clustered instance of Sql server 2018R2 as follows
Virtual Server Name                                                    IP SqlVir 10.1.1.6 SqlVirDr   10.1.1.12
The Data is SqlVir is replicated manually every day to SqlVirDr.
We had to change the Virtual Server name of SqlVirDr to SqlVir so that all dot net applications accessing SqlVir could continue to access the database without changing the application string.
For that purpose I deleted the computer name SqlVir from the domain and its IP 10.1.1.6 from DNS. Then I went to the failover cluster manager of SqlVIrDr right clicked the Sql services selected the properties and changed the DNS name from SqlVirDr to SqlVir.The applications then could access the data.However when I changed the network IP address from 10.1.1.12 to 10.1.1.6 the Sql services was found to be down.
Perhaps the procedure I followed in deleting the computer name from domain and the IP from DNS was wrong.What exactly is the steps that I should follow to achieve the above objective.
