.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

Prevent Excel access to SQL 2000

Posted By:      Posted Date: September 10, 2010    Points: 0   Category :Sql Server
 
I see many questions on how to access SQL from Excel. I'm trying to do the opposite. We have a few purchased applications that use SQL as the database engine. With the capabilities in Excel 2007 making it so easy to connect to the SQL server, we see this as a security issue. We don't want our users to be able to access the database tables through any other methods besides through the application interface (a thick client in one instance and a web-based app in another). How can this be prevented? We are using AD for authentication and authorization and SQL 2000. Our DBA doesn't think this can be prevented. I'm the secuity guy and I'm responsible for data integrity. Are application roles a possibility? I understand they're difficult to maintian. From what I understand, this would allow the application to submit credentials to SQL without relying on AD credentials for individual users. Can SSL be used to encrypt the communications on both the thick and IIS applications? Thanks in Advance


View Complete Post


More Related Resource Links

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

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???

SQL Server 2008 Powershel trying to access SQL Server 2000

  
Below is a script from my SQL server 2008 PowerShell trying to connect a SQL Server 2000 instance. PS SQLSERVER:\SQL\SS2K8\DEFAULT\databases> cd \ PS SQLSERVER:\> cd sql PS SQLSERVER:\sql> cd MySS2KMachine PS SQLSERVER:\sql\MySS2KMachine> cd default PS SQLSERVER:\sql\MySS2KMachine\default> cd Databases PS SQLSERVER:\sql\MySS2KMachine\default\Databases> dir and I see a list of all databases on the default (SS2K) instance. I also see al instances on the machine: PS SQLSERVER:\sql\MySS2KMachine\default> cd .. PS SQLSERVER:\sql\MySS2KMachine> dir Instance Name ------------- DEFAULT MSSQL2005 As you can see from the above, there is also a SQL Server 2005 instance installed on the MySS2KMachine. Now, if I try the same thing for a Sql Server 2000 instance on another server (Windows 2003 R2 with SP1) with no SQL Server 2005 or higher installed on the machine, I get the error below: PS SQLSERVER:\> cd sql PS SQLSERVER:\sql> cd MyPureSS2KMachine WARNING: Could not obtain SQL Server Service information. An attempt to connect to WMI on 'MyPureSS2KMachine' failed with the following error: SQL Server WMI provider is not available on MyPureSS2KMachine. --> Invalid namespace Set-Location : Cannot find path 'SQLSERVER:\sql\MyPureSS2KMachine' because it does not exist. At line:1 char:3 + cd <<<< MyPureSS2KMachine PS SQLSERVER:\sql> So it see

How to access Macros in excel throw DotNet

  
Hi I want to use Macros in Excel throw DotNet. Give simple examples. Can you please any body, give some ideas.   thanks in advance. 

Anonymous access on Excel Viewer web part for a no access .xslx file?

  
I have a public facing SharePoint 2010 Enterprise website with anonymous access enabled. I'd like to present information (a pivot chart) from an .xslx file using the Excel Viewer Web Part. It works fine when I place the .xslx file in a document library that the anonymous users can access. I would, however, like to remove anonymous access from the .xslx file and only expose the chart from the .xslx file using the Excel Viewer web part. When I alter the library permissions to remove anonymous access to the library that holds the .xslx file, the Excel Viewer web part displays an "Access Denied" message to the anonymous user. Is there some other way to achieve this?

Choice Filter Web part and Excel Web Access web part

  
I have a choice filter web part where in I have months like Jan,Feb,Mar etc and a excel web access web part. The choices in the filter web part should determine the URL of the workbook for the excel web access web part. In the filter web part I gave the choices as Jan;http://sitename:port/ExcelList/MyExcel.xlsx where "ExcelList" is the library that stores the excel files for each of the months. But the excel web part always says that "The file selected couldnt not be found". Is it possible for me to know the actual URL getting passed to the excel web access web part? Pl suggest if there is a better way to do this. Cutloo

Convert from Number to Text- Exporting to Excel from SQL Reporting services 2000

  
I was trying to export a report which contains a number format. When I do that, all the numbers in excel will have a green small tag beside it saying "Convert from Number to Text". Is there anyway that I can change the format to a number when I export it to excel?

Is it possible to access an Analysis Server with Excel via http?

  
Hi, in our scenario we want to give another company access to out analysis server via web, their front end should be Excel. So first of all I made a setup like descriped here http://technet.microsoft.com/de-de/library/cc917711(en-us).aspx (but I have really no idea how to test it in an easy way because using MDX sample application ends up every time in "The connection either timed out or was lost.") But apart of that, is it possible to access an analysis server with Excel via http? I did not found any possibility to use a http-adresse in Excel as server path. Any ideas? Thanks and regards Peter  

Excel Services, Kerberos, and cube access

  

Hello Sharepoint experts:

I'm testing our recently set up Kerberos with Sharepoint, Excel Services, and some SSAS Cubes.

I create an Excel Pivot table using a Trusted Connection to a cube, the "Refresh Data when opening file" is checked. The file is published to a Trusted Excel Services location in Sharepoint. I then create a Dashboard and add an Excel Web Part to display that pivot table. All works fine on my machine. When I get another user (one who does NOT have access to this cube) to go to the Dashboard page, that user can see all the data in the Pivot table.

If I then DELETE the cube that the Pivot table is based on, when the user (who never had access to it in the first place) goes to the Dashboard page, they get an error stating that the source may be unreachable or that access may be denied. This error is expected. But what I DON'T expect is to NOT receive an error the first time

Seems that even if the user does NOT have access to the cube, Excel Services still lets them see the 'last saved version of the file' even though it is supposed to query the database for new results every time.

AND when I restore the cube WITH CHANGES, that user can then not only see the pivot table again, they can see the recent changes, even though the original file in Trusted Excel Services Locations was never updated.

Is it possi

Excel Services / Web Access Line Chart Rendering

  
I have noticed in beta 2 that line charts are significantly pixelated / low res when the chart is rendered via Excel Services / Web Access, as compared to how the full Excel client renderes it.  Has this been fixed in the production release of Sharepoint 2010?  If not, are there any plans to improve this later? Our user community likes almost everything else but this about Excel Serivces. Unfortunately, the lack of presentation quality line charts in Excel Services maybe enough to disqualify our wide spread adoption of this great tool.  Help!

Import Access table or Excel spreadsheet into Oracle table

  

I am trying to import from an Excel spreadsheet or an Access database into an Oracle table.  I used the SSIS Import and Export wizard to create the SSIS packages, but everytime that I attempt to run the package, SSIS stops once it gets to the Pre-Execute phase.  There are never any errors.  Am I doing something that cannot be done?

I'm using the following:  SSIS 2005, Microsoft Oracle OLEDB provider (MSDAORA.1), Excel and Access 2003

By the way, I am able to successfully export from the Oracle database to either Excel or Access using SSIS.


Troy

Access 2007 connection to SQL 2000 REALLY slow

  

I have an Access2000 ADP that I want to run under Access2007.  The problem I have is that some forms take up to 45 seconds to open in Access2007!  These are not complicated forms--just simple navigable reference forms like setting up transaction types etc. that are based on basic select statements like:

SELECT * FROM ArReceivableType

Where ArReceivableType is a reference table (less than 10 columns, all int or nvarchar(100) max) containing about 15 or 20 rows.  They open instantly in Access2000.

I put a trace on to see what is happening on the SQL Server, and I noticed heaps of nasty code like this that generates tens of thousands of reads:

select object_name(sotblfk.id), user_name(sotblfk.uid), object_name(sotblrk.id), user_name(sotblrk.uid) from sysreferences srfk, sysobjects sofk, sysobjects sotblfk, sysobjects sotblrk where srfk.constid = sofk.id and srfk.fkeyid = sotblfk.id and srfk.rkeyid = sotblrk.id and user_name(sofk.uid) = N'dbo' and object_name(sofk.id) = N'FK_FaAssetTransactionWork_ArReceivableType_ArReceivableTypeId'

It looks like Access2007 is reading all of the constraints for the underlying table, including all foreign keys.  My SQL database contains 1400+ tables all with properly constructed foreign keys and other constraints.

Any suggestion on how to NOT have Access2007 do this?  Right now, Access2000 works gr

Microsoft Office Excel cannot access the file.There are several possible reasons: . The file name or

  
Hi all,
I am workin on Arc IMS web application project where I will be uploading an excel file containing data say city,addresszip(column headers in excel file)..etc..and load necessary column data in listboxes..and finally save the data to an excel file..when I run though Studio I dont get error..when I run through browser..I got errors ..I even modified DCOMCNFG...But..I get the below error..
Microsoft Office Excel cannot access the file 'E:\vijay\ArcIms\Excelsheets'. There are several possible reasons: • The file name or path does not exist. • The file is being used by another program. • The workbook you are trying to save has the same name as a currently open workbook.
xml
Here goes my code..
Imports Excel
Imports System.Data.OleDb
Imports System.Data
Partial Class _Default
    Inherits System.Web.UI.Page
    Dim connection As New System.Data.OleDb.OleDbConnection
    Dim command As New System.Data.OleDb.OleDbCommand
    Dim dr As System.Data.OleDb.OleDbDataReader
    Dim cmd1 As New System.Data.Ol

Microsoft Excel cannot access the file

  

I am trying to import an excel content to a SharePoint list. When I choose an excel file and import I am saving the file Using "Microsoft.Office.Interop.Excel" object to the temporary folder location and reading it from there. When I try opening a file using "Microsoft.Office.Interop.Excel.Application.Workbooks.Open" I am getting the following error.

Microsoft Excel cannot access the file "TEMP" folder. There are several possible reasons:

• The file name or path does not exist.
• The file is being used by another program.
• The workbook you are trying to save has the same name as a currently open workbook.

I have tried impersonating the INTERACTIVE USER account. But this looks like the excel object doesn't have permission. Please share your inputs on this issue.


Microsoft Office Excel cannot access the file 'C:\inetpub\wwwroot\ProjectWebSite\Files\ROUTINE_MCA.

  

Hello everybody.

I am in a critical situation. I first tried to make a project (file->new->project) using visual studio, where I displayed the content of a excel file by GridView. I was successful then, but when I attach it to my website located in inetpub I am getting this error :

Microsoft Office Excel cannot access the file 'C:\inetpub\wwwroot\ProjectWebSite\Files\ROUTINE_MCA.xlsx'. There are several possible reasons:

. The file name or path does not exist.
. The file is being used by another program.
. The workbook you are trying to save has the same name as a currently open workbook.


At first I faced some COM related problem and found solution in Google, but this particular problem is not solving. Is there any way to access Excel document in asp.net website?

In my application there is a facility of uploading excel document to the server and then display its content. But I have failed to do this display operation, is there any turn around way, like store the excel document as dataset and then access it as required.

Please help. I am in great trouble. Thanks in advance.


SQL Server 2000 Analysis Services & Excel 2003 Add-in

  
I've been working with SQL Server 2000 AS cubes and presenting them in pivot-tables in Excel 2003 with no problems.

I was recently looking at other BI tools I could use and stumbled across the Excel Add-In for Analysis Services, which adds a Cube Analysis menu to Excel and seems to have lots of functionality I could use.

But! It's no longer supported by Microsoft.

I managed to get a download of it from www.download.com and searched for help and I have some good documents to help me understand how to use it, but my problem is that it just doesn't work! I've uninstalled and reinstalled and rebooted, but to no avail.

I can connect to my data source and cube and I can view the dimensions and measures, but when I click apply on them and select the area of the report to put them into, they just don't appear. I can briefly see an EMBED command appear in the formula bar and then disappear, but nothing happens!

I've been through all the trouble-shooting and checked the settings of Excel as requested, but nothing works. 

Has anyone else had this problem or have any suggestions as to how to fix it?

How to access an excel s/s on SP via SQL Server

  

Is it possible to access data from an Excel SpreadSheet hosted on SharePoint (2007 MOSS 64 bit)  via SQL Server?  We have a requirement to calculate metrics where part of the data is in SP Lists, and part of the data is in Excel spreadsheets in SharePoint.

Any feedback is greatly appreciated!


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