.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Register
 
Win Surprise Gifts!!!
Congratulations!!!


Top 5 Contributors of the Month
Easy Web
Imran Ghani
Post New Web Links

Linked Server to DB2 Security Issue

Posted By:      Posted Date: April 14, 2011    Points: 0   Category :
 

Here's the situation.

I have a SQL 2008 box (with latest updates...ver 10.0.4279.0) where I've created a linked server to DB2 ver 9.5 on linux.

I have a local windows group of users who are sysadmins on the SQL Server, but not Administrators on Windows. Also, the SQL Server & Agent service are running as Windows Administrators. (this is a dev box).

Here's the simple code I used to create the linked server:

EXEC master.dbo.sp_addlinkedserver @server = N'DB2BOX', @srvproduct=N'MDASQL', @provider=N'MSDASQL', @datasrc=N'DB2MACHINENAME', @location=N'System', @provstr=N'Provider=MSDASQL.1;Password=xxxxxx;Persist Security Info=True;User ID=db2userid;Data Source=DB2MACHINENAME'
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'DB2BOX',@useself=N'False',@locallogin=


View Complete Post


More Related Resource Links

SQL Server 2008 Linked Server SELECT INTO issue

  
I have a SQL Server 2008 installation running on a clustered Windows Server 2008 R2. I am trying to execute a query on a remote SQL Server to create a table. In order to do so, I call a stored procedure on the remote SQL Server. The stored procedure's description is as follows: ------------------------------------- Create procedure [dbo].[USP_RemoteExec] @varSQL varchar(max) as declare @tempSql nvarchar(max); set @tempSql = CONVERT(nvarchar(max),@varSQL); exec sp_executesql @tempSql ------------------------------------- I pass the following command to the remote stored procedure as follows: exec [servername].[dbname].dbo.USP_RemoteExecute @varSQL='if object_id(''testdb.dbo.tmp_testEmpty3'') is not null drop table testdb.dbo.tmp_testEmpty3; create table testdb.dbo.tmp_testempty3(col1 int, col2 varchar(20));' go select * from [servername].testdb.dbo.tmp_testEmpty3 go This statement returns the following: --------------------------------- col1,col2 (0 row(s) affected) But, when I run the following statement: exec [servername].[dbname].dbo.USP_RemoteExecute @varSQL='if object_id(''testdb.dbo.tmp_testEmpty'') is not null drop table testdb.dbo.tmp_testEmpty; select * into testdb.dbo.tmp_testEmpty from (select top 10 mytab.[col1] as [col1] , mytab.[col2] as [col2] , mytab.[col3] as [col3] FROM testdb.dbo.mytab as [mytab] with (nolock)) as A' go select * from [

SQL Server 2008 Linked Server SELECT INTO issue

  
I have a SQL Server 2008 installation running on a clustered Windows Server 2008 R2. I am trying to execute a query on a remote SQL Server to create a table. In order to do so, I call a stored procedure on the remote SQL Server. The stored procedure's description is as follows: ------------------------------------- Create procedure [dbo].[USP_RemoteExec] @varSQL varchar(max) as declare @tempSql nvarchar(max); set @tempSql = CONVERT(nvarchar(max),@varSQL); exec sp_executesql @tempSql ------------------------------------- I pass the following command to the remote stored procedure as follows: exec [servername].[dbname].dbo.USP_RemoteExecute @varSQL='if object_id(''testdb.dbo.tmp_testEmpty3'') is not null drop table testdb.dbo.tmp_testEmpty3; create table testdb.dbo.tmp_testempty3(col1 int, col2 varchar(20));' go select * from [servername].testdb.dbo.tmp_testEmpty3 go This statement returns the following: --------------------------------- col1,col2 (0 row(s) affected) But, when I run the following statement: exec [servername].[dbname].dbo.USP_RemoteExecute @varSQL='if object_id(''testdb.dbo.tmp_testEmpty'') is not null drop table testdb.dbo.tmp_testEmpty; select * into testdb.dbo.tmp_testEmpty from (select top 10 mytab.[col1] as [col1] , mytab.[col2] as [col2] , mytab.[col3] as [col3] FROM testdb.dbo.mytab as [mytab] with (nolock)) as A' go select * from [

Linked Server UPDATE issue

  

Hello forum,

I'm doing a simple data load between two linked servers, and everything is working fine until I include an UPDATE statement, that's when I receive the following error message.

OLE DB provider "SQLNCLI10" for linked server "ServerName" returned message "The partner transaction manager has disabled its support for remote/network transactions.".

Msg 7391, Level 16, State 2, Line 28

The operation could not be performed because OLE DB provider "SQLNCLI10" for linked server "ServerName" was unable to begin a distributed transaction.


the Distribute Transaction Coordinator service is running on all machines, and I'm able to do INSERTs between the two servers. Here is a part of the code I'm using. It works fine if I don't include the UPDATE. 


BEGIN TRAN
DECLARE @TempTable TABLE(PDID INT
			,FirstName VARCHAR(MAX)
			,LastName VARCHAR(MAX)
			,Degree 

Issue with Connection of Sybase in SSIS and Linked Server in SQL Server

  
Iam having problem connecting with Sybase from SSIS or Link Server of SQL Server 2008.

 I have installed Sybase 12.5.1 OLE DB Driver and have also registered by running command:

 C:\sybase\DataAccess\OLEDB\dll>regsvr32 sybdrvoledb.dll

 We are also able to create System DSN from ODBC Administrator in Windows 7. I am also able to import data in the MS Access. Here is the System DSN I created, successfully.

 I would really appreciate you can help us out.


Linked server access issue when hosting application in IIS

  

Hi,

I have two instance of database and connected the other with linked server. When i run the application locally its working fine. It gets valur from linked server tables. After hosting the application in IIS im getting following eror.

The OLE DB provider "SQLNCLI" for linked server "LINK" does not contain the table ""90"."dbo"."table"". The table either does not exist or the current user does not have permissions on that table.

 

Can anyone specify what user should be mapped to solve this issue?


Linked server issue when application hosted in IIS

  

Hi,

I have hosted my application in IIS. Application has two instance of sql server database connected through linked server. After hosting the aplicaiton in IIS im getting the following error.

SCHEMA LOCK permission denied on object.

 

Can anyone help?


Linked server issue on test box, post upgrade 2005 to 2008 R2

  

In an effort to do some testing prior to upgrading our environment to 2008 R2, I made a test instance on our Dev box.  2005 instance, copied as many things as I could think of from various other instances.  Made a basic linked server to our main cluster, had a repeating job to email me results of a query across that link every few hours.

 

Everything was working fine until the upgrade finished.  It completed at ~7pm.  The email at 6PM came through fine (while the upgrade was in progress), the email at 8pm didn't come through.  Checked various things (DB mail was still working, tested fine).  It couldn't access the data across the linked server.  I tried deleting that link and remaking it.  Errors out.  Tried running the same scripts we use to create our standard linked servers, error out.  The only ones that I can set up and function are links to other instances on the same box.

I've looked around at other fixes for this error message and none seem to make any difference.  Log in with Domain cred's, log in with the sa account, no difference.  I can connect from other instances & servers back to this one, just not outbound from this one.  And it worked prior to the upgrade I applied to it.  All other instances on the box are 2005 as well.

Here is the message I ge

Issue with linked server 2008 (and R2) not an issue in 2005

  

Hi,

I have been using SQL 2005 successfully to connect to tables in a 10g Oracle data warehouse. I have been using the 11g client.

I have tried several times to get this working in SQL Server 2008 (and R2) and have tried the 10g and 11g clients...

I do a query like Select top 100 * from DWD..DW.IC_TRAN_PND and I get the error: 

"Invalid data for type "numeric".

 

I have read about the reasons for this but is there a way to make it work like it does in SQL server 2005?


Are linked servers an issue on SQL Server 2008 R2 64 bit platform?

  
Recently a Microsoft employee strongly recommended not using linked servers at all.   I have a different thread on this topic which died.  However, I'd like to get feedback regarding linked servers on SQL Server 2008 R2 64 bit platforms.
michelle jenks

issue with linked server from sql server 2008 to access 2003 database

  

hi,

I am trying to create a linked server from sql server 2008 to a secure access 2003 database(database which has a password), whatever options i am trying the result is i am getting the error 7399("Cannot start your application. The workgroup information file is missing or opened exclusively by another user.".) or error 7303.

I am selecting 'Microsoft.Jet.Oledb.4.0' as the provider. I think the problem is somewhere in

the workgroup files

Please help me in creating this linked server.

 

 


Question about KB 2498818 (linked server issue)

  
So we have a linked server setup, and will run into random issues pulling data through the linked server setup, getting this error:

The OLE DB provider "SQLNCLI" for linked server "SERVER" reported a change in schema version between compile time ("A") and run time ("B") for table ""database"."schema"."tablename"".

Stumbled across KB 2498818, which fits our situation exactly (using synonyms, blah blah blah) and indicates the bug is fixed in SQL Server 2008 SP2 CU3. I applied CU3 on the server that's pulling data via the linked server connection, but the issue still persists. We can replicate it by doing an index rebuild/reorg.

Anyone else run into this bug? The article isn't quite clear, so before I spend the $$$ to call Microsoft, anyone have any thoughts on whether I would also need to patch the "source" server as well? That's unfortunately a bit more of an undertaking in terms of downtime, testing, etc. than just patching the server that's pulling the data.

Word Automation Issue in Windows Server 2008 Hosting

  

Hi,

The problem I am posting here is that I was facing nearly 2 weeks around. Any body comes with this stuff please help.

Word Automation in sample ASP.NET(C#) application.

I am using Microsoft.Office.Inetrop.Word Assembly for automation. Here I am reading a XXX.dot template file and fill the contents with dynamic data.

When i am executing my code in localhost:someportnumber the automation is working fine and I could get expected result and when I am hosting in my inetmgr(Windows XP is my OS) it is also working fine.

But the problem is that when I am hosting in my production server(Windows Server 2008 Standard Edition) I am not able to perform automation and results in the following error.

Data: System.Collections.ListDictionaryInternal
Message: Word has encountered a problem.
Source: Microsoft Word

The code gets failed in the following line:

ApplicationClass wordApp = new Microsoft.Office.Interop.Word.ApplicationClass();

Document wordDoc = wordApp.Documents.Add(ref oTemplate, ref oFalse, ref oMissing, ref oMissing); // Error in this line

I cannot able to proceed further. Can anybody please help me in solving this issue?

Thank you.


With Regards,

Ashok



Security Briefs: Add a Security Bug Bar to Microsoft Team Foundation Server 2010

  

Take a peek inside Microsoft's strict development security structure as Bryan Sullivan describes the objective security bug classification system?the "bug bar"?used by internal product and online services teams. He will show you how to incorporate this classification system into your own development environment using Microsoft Team Foundation Server 2010.

Bryan Sullivan

MSDN Magazine March 2010


Hackers Beware: Keep Bad Guys at Bay with the Advanced Security Features in SQL Server 2005

  

Get a peek at the new security features in SQL Server 2005 from a developer's point of view. While there are lots of admin enhancements, there are also plenty of dev-specific security improvements you can take advantage of, such as endpoint authentication and support for the security context of managed code that executes on the server. Here Don Kiely elucidates.

Don Kiely

MSDN Magazine June 2005


Intrusion Prevention: Build Security Into Your Web Services with WSE 2.0 and ISA Server 2004

  

Once you've addressed security in your code, it's time to look at the environment it runs in. Firewalls stop unauthorized traffic from getting into your network, and smart Web service-specific firewalls, like the one that comes with Internet Security and Acceleration (ISA) Server 2004, bring XML intrusion prevention to your system for that added layer of safety.

Dino Esposito

MSDN Magazine November 2004


ISA Server 2004: Developing an Application Filter for Microsoft Internet Security and Acceleration S

  

The beta version of Internet Security and Acceleration (ISA) Server 2004 is now publicly available. It includes a rich SDK with several extensibility mechanisms that allow third parties to integrate their specialized solutions on top of the ISA platform. In this article, the author explores the application filter extensibility mechanism, which enables you to add high-level application layer filtering capabilities to ISA Server and to provide rich content filtering solutions. He also highlights the new features of the ISA Server 2004 SDK, then moves on to describe how to develop a basic application filter that monitors all data going through the ISA Server, and how to integrate a filter into the ISA Server management console to create a seamless interface experience for your users.

Yigal Edery

MSDN Magazine March 2004


Windows Server 2003: Discover Improved System Info, New Kernel, Debugging, Security, and UI APIs

  

There's a lot to say about Windows Server 2003. First of all, it's the first operating system with built-in .NET Framework support, and it's the first 64-bit OS from Microsoft. But wait, there's more! There are lots of new features and APIs in this version as well. For instance, Windows Server 2003 features Hot Add Memory and a number of other arcane new tidbits. There are new APIs for handling threads, directories, and files, and new features like the low fragmentation heap for managing memory and system information. There's vectored exception handling and new UI APIs as well.OS internals expert Matt Pietrek takes a look at the additions he finds most interesting and useful so you'll have a good place to start when you dive into Windows Server 2003.

Matt Pietrek

MSDN Magazine June 2003


Categories: 
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend