SQL Server 2014 :: Linked Server To File Folder Security

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


ADVERTISEMENT

SQL Server Admin 2014 :: Check If UNC Path Exists (It Is Folder - Not File)

Oct 29, 2015

usual way to check if file exists

DECLARE @File_Exists INT
EXEC Master.dbo.xp_fileexist 'serverBSQL_Backupfile.bak', @File_Exists OUT
print @File_Exists
1

And if check folder, can use "nul", but it doesn't work for UNC path

DECLARE @File_Exists INT
EXEC Master.dbo.xp_fileexist 'serverBSQL_Backupul', @File_Exists OUT
print @File_Exists
0

If use xp_subdirs like:

EXEC master.dbo.xp_subdirs 'serverBSQL_Backups'

If the folder doesn't exists, Msg 22006, Level 16, State 1, Line 3 xp_subdirs could not access 'ServerBSQL_Backups*.*': FindFirstFile() returned error 67, 'The network name cannot be found.'

how to check if UNC folder exists in Backup? in my code I want to check if the unc folder exists before doing backup, the unc path is retrieved from other table or backup history.

View 6 Replies View Related

SQL Server 2014 :: Query Store Folder Not Visible

Jul 16, 2015

As SQL Server 2016 category is not created so I am posting this query in SQL Server 2014 window.

I have enabled Query Store in SQL Server 2016 using ALTER DATABASE and later tried with Properties of Database too. But I am not able to see the Query Store subfolder under database.

View 6 Replies View Related

How Do I Add An Importer As A User In My Sql Server Database Security Folder.

Dec 28, 2007

How do I add an Importer as a user in my sql server database security folder.
Regards
Karen

View 1 Replies View Related

SQL Server 2008 :: Hiding The Security Folder From Contractors Using SMSS?

Aug 19, 2015

I am setting up a database for one contractor who needs to access one of our databases but i need to remove the permission for them to view the security folder so he cant access the SA account and enable it. I was able to deny them the view permission to see all the other databases and hide the system objects but i can still see the security folder and the sa account there where i can disable and enable it.

View 9 Replies View Related

SQL Server 2014 :: Loop And Query CSV Files In Folder Using Union All Query To Form Resultant Table On Server?

Jun 27, 2014

I am trying to run a UNION ALL query in SQL SERVER 2014 on multiple large CSV files - the result of which i want to get into a table in SQL Server. below is the query which works in MSAccess but not on SQL Server 2014:

SELECT * INTO tbl_ALLCOMBINED FROM OPENROWSET
(
'Microsoft.JET.OLEDB.4.0' , 'Text;Database=D:DownloadsCSV;HDR=YES',
'SELECT t.*, (substring(t.[week],3,4))*1 as iYEAR,
''SPAIN'' as [sCOUNTRY], ''EURO'' as [sCHAR],

[Code] ....

What i need is:

1] to create the resultant tbl_ALLCOMBINED table

2] transform this table using PIVOT command with following transformation as shown below:

PAGEFIELD: set on Level = 'Item'
COLUMNFIELD: Sale_Week (showing 1 to 52 numbers for columns)
ROWFIELD: sCOUNTRY, sCHAR, CATEGORY, MANUFACTURER, BRAND, DESCRIPTION, EAN (in this order)
DATAFIELD: 'Sale Value with Innovation'

3] Can the transformed form show columnfields >255 columns i.e. if i want to show all KPI values in datafield?

P.S: the CSV's contain the same number of columns and datatype but the columns are >100, so i dont think it will be feasible to use a stored proc to create a table specifying that number of columns.

View 9 Replies View Related

Share A RSS File For Applying AD-USER To Folder SECURITY?

Mar 19, 2008

Has anyone written a RSS file that is used to add new Active Directory User/Group to SSRS, and applying security ROLES such as BROWSER?

I have new IT AD group that I need to apply to ALL folders on my PROD, QA and DEV servers. Way too many folders to do via SSMS or Web interface.

Would you be willing to share the RSS file?

View 1 Replies View Related

SQL Server Admin 2014 :: Error While Updating Data Using Oracle Linked Server

Sep 11, 2015

We have oracle linked server created on one of the sql server 2008 standard , we are fetching data from oracle and updating some records in sql server . Previously its working fine but we are suddenly facing below issue.

Below error occurred during process .

OLE DB provider "OraOLEDB.Oracle" for linked server "<linkedservername>" returned message "".
Msg 7346, Level 16, State 2, Line 1
Cannot get the data of the row from the OLE DB provider "OraOLEDB.Oracle" for linked server "<linked server name>".

View 7 Replies View Related

SQL Server 2014 :: Call For DB Backup For A Database On Another (linked) Server

Jul 8, 2015

I have a sproc that will generate a dynamic call (and this must be done in a stored procedure),... Anyway the dynamic call I am having problems with is that I need to dynamically create a statement to backup a database. It works as long as the database is on the same server / instance as the stored procedure. I want to be able to from server/instance A create a command that does a backup of a database that is on server B. I just can't figure out the syntax for a database backup where the database is on another server.

Trying something like [ServerName].[DatabaseName] does not work...

View 9 Replies View Related

SQL Server Admin 2014 :: Access A Database Without Linked Server

Jul 16, 2015

We are upgrading from SQL 2008R2 to SQL2014 but we have discovered that a couple of our applications are not supported on 2014. We'd like to keep one 2008R2 server and one 2014 server until we have time to upgrade the applications and move everything to the new server. The problem is we have custom code in some of the 2014 databases that access tables in the 2008 databases.

I know we can easily do cross server joins by using a linked server, but it would be a huge undertaking to find all that code and add a linked server name in front of every table, stored procedure, etc. So my question is, is there any way to move a database to a different server and still be able to access it without having to qualify the object names with a linked server? Is there some kind of server/database synonym that can be setup that would be recognized by all databases?

View 8 Replies View Related

SQL Server 2014 :: Find Table In A Linked Server?

Feb 2, 2015

I have multiple linked servers and trying to find if a table (approximate table name) exists in one of the linked servers.

I have a code to find it in local DB....how can I modify to include all linked servers as well:

ALTER PROCEDURE [dbo].[FindTable]
@TableName VARCHAR(256)
AS
DECLARE @DBName VARCHAR(256)
DECLARE @varSQL VARCHAR(512)

[code]....

View 3 Replies View Related

SQL Security :: Can't Connect To Server 2014 When TLS 1.0 Is Disabled

Jun 24, 2015

After installing SQL Server 2014 SP1 CU1 (build version 12.0.4416.1) the SQL Server service no longer fails to start when TLS 1.0 is disabled, but no matter what I have tried I can't successfully connect to the database.When trying connect using shared memory I get this message:A connection was successfully established with the server, but then an error occurred during the login process. (provider: SSL Provider, error: 0 - No process is on the other end of the pipe.) (Microsoft SQL Server, Error: 233)And if I tryTCP/IP or Named Pipes I get this:

A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified) (Microsoft SQL Server, Error: -1)

TLS 1.1 and TLS 1.2 are enabled on the computer and working fine for IIS. what I can do to get SQL Server to work successfully with TLS 1.2 or is it still just not supported yet?

View 8 Replies View Related

Security In Linked Server

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

SQL Server Admin 2014 :: PowerShell Security Error

Jul 16, 2015

I have a SQL Server 2014 instance running on a SQL Server 2008 R2 server. The server is not clustered, it is just a stand alone SQL Server. The syspolicy_ purge_history job fails every now and then with the error message: "A job step received an error at line 1 in a PowerShell script. The corresponding line is 'import-module SQLPS -DisableNameChecking'. Correct the script and reschedule the job. The error information returned by PowerShell is: 'Access to the path 'PowerShell_CommandAnalysis_Lock' is denied. '. Process Exit Code -1. The step failed."

Google isn't bringing up much besides the whole"If this is a clustered server make sure you have the right server name in the command" answer, which isn't the case here. Some days this job fails and some days it succeeds. I have checked out task scheduler to see if there were any conflicts there, found nothing. Nothing in the event viewer either.

View 2 Replies View Related

Linked Server Security Question

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

Linked Server Security Doubt

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

SQK2K - Linked Server Security.

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

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 View Related

SQL Server 2014 :: Select On Linked Databases

Sep 18, 2014

I am trying to find out what records on in one DB and not the other. This SQL statement works find if the DBs are on the same Server. If I replace on the DBs with a linked DB (on another server) I get the following error.

SELECT *
FROM [LinkedServer].[DBonLinkedServer].[dbo].[Sales Price]
WHERE NOT EXISTS
(SELECT [LocalDB].[dbo].[Sales Price].*
FROM [LocalDB].[dbo].[Sales Price]
WHERE ([LocalDB].[dbo].[Sales Price].[Item No_] collate Latin1_General_CS_AS = [LinkedServer].[DBonLinkedServer].[dbo].[Sales Price].[Item No_]) AND
([LocalDB].[dbo].[Sales Price].[Unit of Measure Code] collate Latin1_General_CS_AS = [LinkedServer].[DBonLinkedServer].[dbo].[Sales Price].[Unit of Measure Code]) )

Msg 4104, Level 16, State 1, Line 3
The multi-part identifier "LinkedServer.DBonLinkedServer.dbo.Sales Price.Item No_" could not be bound.

Msg 4104, Level 16, State 1, Line 4
The multi-part identifier "LinkedServer.DBonLinkedServer.dbo.Sales Price.Unit of Measure Code" could not be bound.

View 2 Replies View Related

SQL Server Admin 2014 :: How Does Security Works Between Availability Groups

May 21, 2014

how does security works between availability groups.

ex if i create an object and grant permissions to a user will that be replicated to secondary replica .

View 4 Replies View Related

SQL Server Admin 2014 :: Any Kind Of Security Risks For Enabling CLR?

Jan 21, 2015

SSISDB 2014 - Enabling CLR - Is there are kind of security risks for enabling CLR when creating SSISDB?

View 0 Replies View Related

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 View Related

How Is Security To FoxPro Managed Through Linked Server From SQL

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

SQL Security :: Query Linked Server Configuration?

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

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?

View 4 Replies View Related

SQL Security :: Unable To Connect Via Linked Server

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

Linked Server Security On BUILTIN Versus SA

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

How To Change Login In Linked Server (From Sql Security To Integrated Sec)

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

SQL Security :: Jdbc Linked Server Call Fails

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

SQL Security :: Create Linked Server Using Windows Authentication

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

Current Security Context Not Trusted When Using Linked Server From SAP

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

SQL Security :: Connecting Linked Server Using Windows Credential

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

SQL Server Admin 2014 :: How To Delete A Login Linked With Endpoints

Sep 1, 2015

I am planning to delete a login from SQL logins because he moved out from project .when i try to delete the login , it throws an error saying " The server principal owns an endpoint and cannot be dropped , error 15141 "

Same problem facing on different servers.

Note : Environment is SQL 2012,SQL 2008 including cluster servers .

View 2 Replies View Related







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