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


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

Linked Server And User Combinations

Posted By:      Posted Date: October 28, 2010    Points: 0   Category :Sql Server
 

Hello Experts,

I have created a linked server by using the following script:

exec sp_addlinkedserver @server='MyConnection',@srvproduct=',@provider='SQLOLEDB',@provstr=
  'user=sa;pwd=XXXX;Integrated Security=SSPI;Initial Catalog=master;Server=DOMAIN\PCNAME'

I would like to retrieve data by using a Sql server agent job. And basically, I have used a t-sql script such as:

delete RPDW2010.dbo.dimClass_MAX
Go
insert into RPDW2010.dbo.dimClass_MAX 
select *
from MyConnection.MAX.dbo.vw_dimClass_MAX

I have tried to run this job by using different user combinations:

  • NT Authority / System
  • sa
  • DOMAIN NAME/ Administrator

I get always the same error even I change the user name:

Executed as user: dbo. The server principal "sa" is not able to access the database "RPDW2010" under the current security context. [SQLSTATE 08004] (Error 916).  The step failed.

All the local and remote server in the same domain and I have googled several days to solve this problem.

 

Any idea?




View Complete Post


More Related Resource Links

Linked Server And User Combinations

  

Hello Experts,

I have created a linked server by using the following script:

exec sp_addlinkedserver @server='MyConnection',@srvproduct='',@provider='SQLOLEDB',@provstr=
  'user=sa;pwd=XXXX;Integrated Security=SSPI;Initial Catalog=master;Server=DOMAIN\PCNAME'

I would like to retrieve data by using a Sql server agent job. And basically, I have used a t-sql script such as:

delete RPDW2010.dbo.dimClass_MAX
Go
insert into RPDW2010.dbo.dimClass_MAX 
select *
from MyConnection.MAX.dbo.vw_dimClass_MAX

I have tried to run this job by using different user combinations:

  • NT Authority / System
  • sa
  • DOMAIN NAME/ Administrator

I get always the same error even I change the user name:

Executed as user: dbo. The server principal "sa" is not able to access the database "RPDW2010" under the current security context. [SQLSTATE 08004] (Error 916).  The step failed.

All the local and remote server in the same domain and I have googled several days to solve this problem.

 

Any idea?


Linked server fails -Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'. with SQL2008R2

  

Hi, Kerberos seems to be malfunctioning with SQL2008R2.

 

I have a new dev SQL2008R2 server called BDSDEV2 and an upgraded(in-place) SQL2008R2 server BDSTEST2.

From BDSDEV2 , I can successfully issue a linked server query to BDSTEST2

select * from bdstest2.eis_prod.dbo.wcs_agent

 

But from my client in SSMS, connected to BDSDEV2  , the double hop is failing to BDSTEST2 for same query 

Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.

 

I have another BDSDEV1 server, which is SQL2008 and there double hop IS WORKING

From my client in SSMS, connected to BDSDEV1  ,I can successfully issue the query to BDSTEST2 .

 

The service accounts  for MSSQLSERVER are both local admins on their servers and sa's

I have confirmed SPN is OK 

This is from BDSDEV2 - sql2008r2 server.

C:\Program Files (x86)\Resource Kit>setspn -L tsqlssvc

Registered ServicePrincipalNames for CN=TSQLSSVC,OU=SvcAcct,DC=corp,DC=saif,DC=c

om

MS SQL Server: Disconnect Users From Database - Kill User Session

  
If you ever wanted to restore your database from a SQL backup file (.bak), but there are still users connected to your database, the backup operation will fail causing the error: Exclusive access could not be obtained because the database is in use.

Configuring User Profiles in SharePoint Server 2010

  
Note: I've added the necessary links relating to SharePoint Server 2010 prerequisites, along with instructions for configuring the user profile synchronization service, at the end of this

User Defined Functions in Microsoft SQL Server

  
User Defined Functions are compact pieces of Transact SQL code, which can accept parameters, and return either a value, or a table. They are saved as individual work units, and are created using standard SQL commands. Data transformation and reference value retrieval are common uses for functions. LEFT, the built in function for getting the left part of a string, and GETDATE, used for obtaining the current date and time, are two examples of function use. User Defined Functions enable the developer or DBA to create functions of their own, and save them inside SQL Server.

No ASPNET User in Windows Server 2008?

  

I have been looking everywhere online regarding this, but have not found a clear answer.

 

I have an ASP.Net app running on Windows Server 2008, it's basically a reporting app using Crystal Reports. I have encountered a roadblock in regards to rendering some pages. When I run the app in Visual Studio(on the same 2008 machine) everything works perfectly. Yet when I publish it and run it live the reports will not display correctly, I get a message saying Failed to render page.

 

I looked this error up in the Crystal Reports website and the fixes they have there refer to giving the windows "aspnet" user full access rights to certain folders on the machine. I would be happy to do this, however there is no aspnet user in Windows Server 2008.

 

I installed the server and set its role to be Web Server. I also checked all the options regarding ASP.Net and IIS 6 compatibility. Yet there is no ASPNET username anywhere.

 

Did I set the server up wrong? or do I have to do something within ASP.Net to  create this user and then give it rights? 


Reporting: Deliver User-Friendly Reports from Your Application with SQL Server Reporting Services

  

SQL Server 2000 Reporting Services--a server-based reporting platform built on the .NET Framework and integrated with SQL Server 2000--lets you easily add reporting from diverse data sources. Using Visual Studio and Reporting Services you can integrate reports from any data source that has an OLE DB, ODBC, or ADO.NET provider into your Web applications. This article explains how it's done.

John C. Hancock

MSDN Magazine August 2004


Need help with setting up SQL Server 2008 for role/user management

  

I've posted my problem in a few other categories.  Sorry for that.  I didn't see the security category until now.

I'm trying to use ASP.NET's built-in role/user management for a couple ASP.NET MVC 2 sites.  I'm using the MVCMusicStore as my guide.  I click on the Configuration Tool as the tutorial suggests, which launches the site administration page.  Unfortunately when I click on the security tab, I get an error that it cannot connect to the data store.  Attempting to test the AspNetSqlProvider gives me a similar error.

I've run the aspnet_regsql program as the error message suggested, and I have a role/user management database ready to go on the server.  The proper tables are constructed, and all that.  The configuration tool still doesn't see the data store.

As a test, I ran the MVCMusicStore demo in the debugger.  Its role/user management functionality worked.  I was able to add myself as a customer, and add/remove items from my cart with no errors or exceptions.  However, when I attempted to access its security tab in the configuration tool, I received the same error as I get for my own project.  How is it possible that role/user management works, but the configuration tool cannot change its settings?

Any suggestions on how I can get the configuration t

Linked Server to access Excel 2007

  

Hi

I'm tried SELECT * INTO XXX FROM OPENROWSET alongwith Microsoft.ACE.OLEDB.12.0.

Apparently the query requires the sql account to have SYSADMIN privileges.

Considering that SYSADMIN should not be provided to a database account on a Production Server, I tried using the Linked Server method.

Following is my code.


Exec sp_addlinkedserver 'AB2','Ace 12.0','Microsoft.ACE.OLEDB.12.0','\\202.46.215.35\sagarr\Test1\cpc\c2\AB2.xlsx',NULL,'Excel 12.0;IMEX=1'
Exec sp_addlinkedsrvlogin 'AB2','false',NULL,NULL,NULL
go
SELECT * INTO [CPCAB2.xlsx] FROM OPENQUERY([AB2] ,'SELECT * FROM [Sheet1$]')
Exec sp_dropserver 'AB2','droplogins'


Now i get the following error

Error.15247-User does not have permission to perform this action

My Excel file, Database and Windows Application run on separate machines.

i have provided the following privileges

GRANT ALTER ANY LOGIN TO sqlaccount
GRANT ALTER ANY LINKED SERVER TO sqlaccount


EXEC sp_configure 'show advanced options', 1
RECONFIGURE
EXEC sp_configure 'ad hoc distributed queries', 1
RECONFIGURE

The DisAllowAdHocProcess in

Generating and storing html pages in User Directories on the server

  

Hi,

I come from corporate asp.net development, and I am now working on a public website which will get a lot of traffic, perhaps a million users a day.  So I am faced with a ton of performance and scalability questions.

I would like to share some thoughts and see if my approaches resonate with any good practices, or perhaps I am heading down the wrong path...  Either way, I want to hear opinions.

(My number one concern is the fact that I am using asp.net for a public website.  I don't know of a lot of websites using asp.net)

I am using asp.net 2.0 (I know, its not the latest technology but I don't need any advanced features of asp.net 3.5 or 4, unless there are significant improvements geared towards what I am doing) 

On launch date, this will be running on a dedicated Windows Server 2008 running on Intel ATOM 330 1MB L2 Cache 1.6Ghz Dual-Core with 2GB DDR RAM.  Its fully managed by a full service hosting company which gives me an option to upgrade to a bigger server within a few hours.  I can even move the site to a webfarm if the traffic demands it.

So I have a User Dashboard, where users can create albums and upload pictures.  So what I am doing

Failed to generate a user instance of SQL Server due to failure in retrieving the user's local appl

  
Hi ,From the last few days I am struggling with the error "Failed to generate a user instance of SQL Server due to failure in retrieving the user's local application data path. Please make sure the user has a local user profile on the computer. The connection will be closed."I hav created an asp.net web application with membership shcema. It works fine an my developement machine but when I published it on my web server. It is giving me above error for which I searched but not found any help.Can anyone help me with this.

SQL Server 2005 Performance Dashboard Reports user context

  
HI, We are planning on installing the Dashboard reports. But we'd like to know if the Dashboard is run under admin user or the login user who runs the Dashboard reports? Thanks! Kong

Creating a table-valued function on a user defined type in SQL Server CLR

  
We would like to be able to create a table-valued function on a user defined type.  We would like the syntax in SQL to look like it works for the XML nodes function:   DECLARE @myXml XML = '<a><b>1</b><b>2</b><b>3</b></a>' ; SELECT  node.query('text()') FROM    @myXml.nodes('a/b') nodes (node) ;   In other words, in the FROM clause, we can access the "nodes" method of the xml variable and it returns a rowset. Thanks!

Linked Server to access Excel 2007

  
Hi I'm tried SELECT * INTO XXX FROM OPENROWSET alongwith Microsoft.ACE.OLEDB.12.0. Apparently the query requires the sql account to have SYSADMIN privileges. Considering that SYSADMIN should not be provided to a database account on a Production Server, I tried using the Linked Server method. Following is my code. Exec sp_addlinkedserver 'AB2','Ace 12.0','Microsoft.ACE.OLEDB.12.0','\\202.46.215.35\sagarr\Test1\cpc\c2\AB2.xlsx',NULL,'Excel 12.0;IMEX=1' Exec sp_addlinkedsrvlogin 'AB2','false',NULL,NULL,NULL go SELECT * INTO [CPCAB2.xlsx] FROM OPENQUERY([AB2] ,'SELECT * FROM [Sheet1$]') Exec sp_dropserver 'AB2','droplogins' Now i get the following error Error.15247-User does not have permission to perform this action If I execute the query from Query Analyzer it works fine, but fails when I execute it using Windows App and encapsulate code in Stored Proc. My Excel file, Database and Windows Application run on separate machines. i have provided the following privileges GRANT ALTER ANY LOGIN TO sqlaccount GRANT ALTER ANY LINKED SERVER TO sqlaccount EXEC sp_configure 'show advanced options', 1 RECONFIGURE EXEC sp_configure 'ad hoc distributed queries', 1 RECONFIGURE The DisAllowAdHocProcess in Registry has value 0 Please let me know what additional permissions should i set to get it working???

Linked server to access 2010

  
I keep getting an error when setting up a linked server in sql 2008 R2 x64 to a microsoft access database file (mdb or accdb file).  (The problem occurs on computers with either the 32-bit or 64-bit version of ms office installed).  What are the proper connection string settings?  (This is after right clicking linked servers in ssms and selecting "add new linked server")  The below settings don't work: Provider (from dropdown): Microsoft Office 12.0 Access Database ENgine OLE DB Provider Product name: Access Data source: C:\foopath\foo.mdb  --also doesn't work for foo.accdb Provider string: Microsoft.ACE.OLEDB.12.0 An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo) Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "MYLINKEDSERVER". OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "MYLINKEDSERVER" returned message "Could not find installable ISAM.". (Microsoft SQL Server, Error: 7303) I got some exotic errors when following the directions in Books Online (under sp_addlinkedserver). For example: OLE DB provider 'Microsoft.Jet.OLEDB.4.0' cannot be used for distributed queries because the provider is configured to run in single-threaded apa

No Way to retrieve data from oracle ref:_cursor over T-SQL and linked Server?

  
Hello, for an migration projekt we want to compare results from sp's from oracle and sqlsserver. Same calls should retrieve same results. Also we want build an automatic test for this. But is there now way to retrieve results from oracle sp's whit rev_cursor over linked Server? No one answer to this: http://social.msdn.microsoft.com/forums/en-us/sqldataaccess/thread/2BAC6743-8701-4476-8F36-0377A5761525   greetings Michael
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