Any Good Whitepapers On Security/deployment For Entire SQL Server BI Solutions?

Aug 1, 2007

At my current employer we are struggling with the best way to manage security and deployment of a project that contains databases, SSIS, SSAS and SSRS components, using configurations.

Environment (Dev):
3 SQL Server databases, all using mixed-mode security, using SQL Server security credentials.
12 SSIS packages; one master package, eleven child packages, 3 shared data sources
1 SSAS database; one cube, 15 dimensions, three referenced data sources from the SSIS project (in same solution)
6 SSRS reports, one data source to cube (not shared- doesn't appear SSRS can share datasources among other projects in the solution? Why?)

Everything runs fine in development. Now comes the tricky part.

Deploying SSIS and SSAS into production environments:

-Packages use XML config files for connection strings to three relational data sources.
-Deploy to SQL Server storage. Deploy wizard copies package dependencies (including XML config files) to default location set in INI file. When I do this, no config file shows up in remote server (remote server not set up identical to local, so directory does not exist. Need UNC path?) So, being a developer with no "special" permissions on the PROD server, what security permissions is allowing the deployment wizard from copying files to this location on a production server?
-Using a deploy script using dtutil doesn't copy the SSIS dependencies. Is this matter of using COPY or XCOPY to copy the configuration files to the dependency location? Again, in real-world practice, do developers typically change this location in the INI file to another location, or stick with the default. In either case, how does security work that allows files to get copied to the remote folder? (i.e. manual, or SQL Server manages this file folder permission through some other magic)
When using SSMS and running the package after being deployed on the remote server, if the config path is the default (e.g. C:program filesMicrosoft SQL Server90DTSPackages...) it appears to be read from the local machines directory rather than the remote machines directory path (do I need to use UNC paths? The wizard doesn't give this option it seems)
-When scheduling the job from SQL Agent, does the proxy account need permissions to the folder the config files sit in?
-What about the roles security on the packages themselves? Where does the server roles come into play (dtsltuser, dtsadmin)
-Because the SSAS project uses connection references to the SSIS project in BIDS, and SSIS project uses configurations, will SSAS pick up on these connections?
-What about impersonation levels for SSAS? Leave all data sources set to default, and set the database impersonation level to "UseServiceAccount"? What if the developer is not the same as the OLAP administrator on the production server? In this case, Use Service Account isn't an option, and neither is the current users credentials.
-SSAS database also has security for Full Control, but still doesn't prevent security at the data source level within the database (talking about impersonation level, not source db credentials)
-How can SSRS connections leverage other shared connections?


As you can see, there are a ton of security considerations, none of which are intuitive and can be configured multiple ways and actually work (and a ton of ways that won't work).

I need a simple cheat-sheet about each step to take to configure this so multiple developers can work without interruption, hot-deploying SSIS, SSAS, and SSRS changes into different environments (QA, PROD).

-Kory

View 2 Replies


ADVERTISEMENT

Script Out The Entire Database Objects - Is There A Good Way?

Jul 27, 1998

After found out the `transfer object` command in MS SQL EM didn`t work well
in my databases, I started code by myself to generate the `Create xxx` statements to copy objects across databases (structure only) using SQL.

I`ve coded out most of them, logins, defaults, UDT, tables, stored procedures,
but then I started to have problems with primary keys and foreign key const.

Has anyone tried to do the same thing before or is there some SQL scripts created to handle this already? Please advise me.

I am thinking to use SQL-DMO with VBscript now. But I still prefer a good
SQL script (old thought, the DB-Lib based `isql` is more reliable than the
SQL-OLE).

Thanks for any suggestion.

- Ken

View 3 Replies View Related

VS Deployment Security

Jul 24, 2007

I have a report project I have deployed onto a remote Reporting Services server several times smoothly and easily. Recently I logged into the site in IE to test the reports using a username and password with MUCH lower security than my own. Since then I cannot deploy reports. I get a message that "The permissions granted to 'domainLoser' are insufficient for performing this operation." This is as it should be. Loser should NOT be able to deploy reports. The problem is I can't get it back to being me, the admin. I have reported, opened the reports in IE using my own login, reopened VS2005, tried setting IE to require authentication to force a login again, and then rebooted again.



Does anyone have a suggestion about how to correct this? Had I known I would NEVER had tested this on the same PC I was deploying from. I seem forever stuck as Loser now.



Thanks for any suggestions or questions for clarification. Eva

View 4 Replies View Related

FAQ: Are There Any Whitepapers About Building A Disaster Recovery Site At A Remote Location For SQL Server 2000

Sep 9, 2006

Hi,

Sorry for the wide distribution.

I'm trying to find any useful whitepapers about how to effectively build and operate a disaster recovery site at a remote location for SQL Server 2000. Does anyone know where to find such information?

I also know that one good option for my customer is using the Mirroring feature of SQL Server 2005. What are the other options? Is Replication an effective one for a mission-critical database (online banking)?

Thanks in advance

View 3 Replies View Related

Security Required To Run Deployment Utility

May 18, 2007

I would like to have my developers responsible for deploying their SSIS packages to the Test/QA environment. I tried granting access to several of the stored procedures in msdb and the sysdtspackages90 table. The only thing that seems to work is granting sysadmin priviliges. Is there a server or database role that will grant the appropriate access? thanks

View 5 Replies View Related

SSIS File System Deployment Vs SQL Server Deployment

Aug 15, 2007

Hi,


Please can you let me know which of the following 2 is a better method to deploy SSIS packages

File System deplyment OR SQL Server deployment

What are the advantages of one over the other?

Thanks,
Mrinali

View 4 Replies View Related

SQL Server 2005 Non-ansi Joins: Any Easy Solutions?

Jul 20, 2006

My company wants me to research and flags or registry tricks that would allow non-ansi joins '=*' and '*=' in SQL Server 2005 with a compatiblity mode of 90 to be allowed.

The way I understand the situation is that in SQL Server 2005 with the database compatiblity set to 90, non-ansi join SQL such as the following would not work.

Select * from
Customer, Sales
Where Customer.CustomerID *= Sales.CustomerID

To work, the SQL above would have to be converted to ansi join SQL such as the following:

Select * from
Customer LEFT OUTER JOIN Sales
On Customer.CustomerID = Sales.CustomerID

Many hours would be spent browsing through millions of lines of code to find the non-ansi SQL and have changes made.

Does anyone know of any trace flaqs or registry entries that would allow SQL Server 2005 work in 90 compatiblity and still allow non-ansi =* and *= joins in SQL?

Thanks,
AIMDBA

View 3 Replies View Related

Database Books And Whitepapers

Jul 20, 2005

Does anyone know of a good book or white papers that compare the relativestrengths, capabilities and features of databases like SQL*Server, Oracle,Informix, MySQL etc? Thanks.

View 1 Replies View Related

Differences Between 2005, 2000, And Express - Documentation, Whitepapers?

Apr 27, 2007

Can anyone point me in the direction of some NON-sales documentation on the differences between these product? I am sure, especially with Express, there are considerable functionality and architecture differences.



I've looked in BOL, and I've done searches online.



All i seem to get is sales related stuff.



I'm curious about the architecture of SQL 2005, SQL express. In 2000, there was some fairly detailed documentaiton on this subject, but 2005 BOL seems REALLY diffucult to find things.



I may just need to try different keywords...

View 4 Replies View Related

Copy Entire DB On Same Server

Jun 3, 2004

Hi All,

Could anybody tell me how to make a copy of an entire database on the same server? The copy wizard won't allow the a copy to be made on the same server.

And I believe I won't be able to detach the database and re-attach it to a newly created database with a different name.

Thanks in advance!

View 1 Replies View Related

Move Entire SQL Server Instance

Jan 29, 2007

I have tried searching all over and have not been able to find what I need, but this is what I have pieced together.

Background:
I need to move an entire instance of SQL Server 2000 SP3 running on Windows Server 2000 to a new physical server running SQL Server 2000 SP4 running on Windows Server 2003. I guess my biggest question is how do I move system databases (master,msdb,tempdb,model), I don't particularly like the idea of using sp_detach/sp_attach on system databases?

This is what I had in mind.
-Install SP4 on the source server
-Backup source to tape
-Restore from tape to destination server (restore system dbs as re_master,re_msdb)
-Shut SQL services down on destination server.
-Replace the system dbs .mdf and .ldf with the re_master & re_msdb .mdf and .ldf
-Restart SQL services
-Restore User databases from tape.

Ultimately, I'm asking can you change the undlying .mdf & .ldf files for system databases?

Also, are there any issues going from Windows Server 2000 to Windows Server 2003?

Thanks in advance.

View 11 Replies View Related

SQL Mgmt Server: Run Selection Vs Entire Tab

May 3, 2007

Has anyone here been having issues where SQL Server Management Server will sometimes ignore selected text and run all scripts on the current tab?

This has been happening most recently after a connection times out or is forced shut. From a disconnected tab, if I add "Use Master" at the bottom of a page full of script, it will often run ALL statements starting from the first line.

I have SP2 installed which rolls management server to 9.00.2047.00 - but this has been an issue for me even before SP1 & SP2.

I can find nothing in the knowledge base regarding this, but perhaps my search string wasn't very thorough.

I'd just like to know if others are experience this issue so I can report to MS.

Thanks.

View 3 Replies View Related

How Can I Get The 'entire' Database, Structure And Data, From SQL Server 2008 To SQL Server 2005?

Jun 20, 2008

1) I can't get the 'copy database' function to work from SQL Server 2008 to SQL Server 2005. I connect ok. Everything goes to the last step and then it fails.2) I cant get a SQL server 2008 backup to restore on SQL SEerver 2005 either.
The only way I know that works is to script the creation of all tables then export and import. This does work.
How can I get the 'entire' database, structure and data, from 2008 to 2005?
ThanksSQL newbie.

View 2 Replies View Related

Please Help With Oltp Solutions

Aug 28, 2007

hi. i dont understand what they mean when they say developing oltp solutions. can anybody pls explain it to me. also does anyone know what ways there are to develop sql oltp solutions using SQL 2005 reporting services, OLTO, Excel Services. as well as any good tutorials for it?

thanks for the help.

View 6 Replies View Related

Replication Solutions?

Jul 20, 2005

Hello everybodyI work at a company in Iceland and we have developed a 3-tier solutionwritten in ASP - Visual Basic - MSSQL2000, 4 companies are using thesolution almost constantly and accessing it through a browser. Theconnection has never gone down (yet) so that it has affected ourclients but we are thinking of how be able to run the solution locallyat every place and then create a replication to a main server that ishosted at our place.My question is: Does it affect speed for the clients that are usingthe solution or is there a better way of doing this?The solution is a ticket sale system and our clients use it every dayand people that sit at home should be able to order ticket online.Because of that we can't update the database every 5 minutes or 15minutes because we don't want a double booking in the same seat.Any help appreciated!- Sindri

View 1 Replies View Related

Solutions And Projects: What Are They And How Do They Help The DBA

Mar 10, 2006

I guess the Subject line sums it all up, but I need some experienced explanation of what do a solution and a project represent, and how do I use them to my advantage.

Is a solution an entire database? If so, how can I create a solution from my existing databases?

Are either of them a way to collect together scripts etc which will be run against a production database when the solution is rolled out?

What is a project? Is it a single set of scripts related to an upgrade to a database? If so, can it be executed as a single entity? How is the sequence of execution controlled?

And so on and so on....

2005 is such a step backwards for DBA's with all the features we used to have and now don't. If it wasn't for the fact that MS will eventually stop supporting 2000, I frankly see no incentive to upgrade myself.

Ok, OK, flame off.

Can anybody suggest some resources which might give me some insight into these questions?

Thanks,

-Rob

View 3 Replies View Related

Search For A Stored Procedure On Entire Server?

Nov 6, 2012

sp_MSforeachdb 'Select ''?'' as Dbname, SPECIFIC_SCHEMA, ROUTINE_NAME
From ?.INFORMATION_SCHEMA.ROUTINES
where ROUTINE_NAME like ''%YourSPName%'''

View 1 Replies View Related

Default Auto Close To No For Entire Server?

Nov 27, 2006

I was having this problem:http://groups.google.com/group/micr...78?dmode=sourcewith it taking an inordinate amount of time to enumerate the databaseson my local SQL Server instance, so I went through all the localdatabases and unchecked "Auto Close" on the "Options" tab of theDatabase properties in Enterprise Manager.This cleared up the problem nicely. In EM or through SQL-DMO, thedatabases on the local server enumerate quite quickly now. However, anynew databases that are created have "Auto Close" checked, so eventuallythe original problem will resurface.Is there some way to tell SQL Server to create any new databases with"Auto Close" set to No?

View 9 Replies View Related

Replication--Too Many Questions---Too Little Solutions

Dec 15, 1998

1) Do I have to install publishing on both servers (A and B) even though one will be publisher and the other will be subscriber.

View 1 Replies View Related

Replication--Too Many Questions---Too Little Solutions

Dec 15, 1998

1) Do I have to install publishing on both servers (A and B) even
though one will be publisher and the other will be subscriber.


2)a. Can named pipes be used for communication between these two servers
which are on the same domain but not on the same network. Why or why
not, whatever the answer may be?
b. If I use TCP/IP, it the connection set up using the client
configuration utility? How is the connection string set up in this
case?
c. Suppose the publishing server was not using Net-Beui. Could this pose
any problems for communication. (is using lmhosts sufficient in this
scenario)


3) I have set up a (remote) SQL Server to be a Publisher/ Distributor.
Both SQL servers have been configured to be remote servers relative to
each other. Following are the steps I have carried out to set up
replication:

______On the Publication Server (a remote server)
I went to Server --> Replication Configuration ---> Install Publishing

Next, I chose a local distribution server. I think that the
instdis.sql script ran fine because the distribution database was
installed successfully.

___Next, I went into Manage Publications from Server menu to set up
the publications.


_____________When I went the subscription server to subscribe to the
published articles, I got the following error message:


Error 14093: [SQL Server] You must be System Administrator (SA)
or Database Owner (dbo) or Replication Subscriber (repl_subscriber)
to execute the stored procedure.




PS
Please Help

View 1 Replies View Related

Bidirectional Replication Solutions

Apr 9, 2008

According to this article from last year:http://searchsqlserver.techtarget.com/tip/0,289483,sid87_gci1251149,00.htmlThese are the main options:* Merge Replication* Bi-directional Transactional replication* Immediate Updating* Queued Updating* Peer to Peer* RDAAre there any new alternatives that have popped up over the last year? Are all of six above still good options based on needs?We currently have a three server topology using merge replication.ServerA (App1DB) <--> ServerB (App2 DB)ServerA (App1DB) <--> ServerB (App3 DB)ServerA (App1DB) <--> ServerCServerA supports 1 intranet application using 1 DBServerB supports 2 extranet applications using 2 DB's (1 per application)ServerC is our DW server that we have installed a Search DB which is used by all applicationsPrior to our "upgrade" to merge replication we were using 1-way Transactional Replication so our topology looked like:ServerA --> ServerB (App2 DB)ServerA --> ServerB (App3 DB)We also had linked servers between ServerB and ServerA as well as between ServerC and ServerA to update data on ServerA. We would simultaneously update/insert the tables on ServerB/C and create custom stored procedures to handle the data already processed from the subscribers.With our new implementation we are seeing more latency as well as locking since merge replication is not running off of transaction logs anymore.My main question is would we see an increase in performance and less locking as a result of a topology like this:Master <--> ServerA (App1 DB)Master <--> ServerB (App2 DB)Master <--> ServerB (App3 DB)Master <--> ServerCWhere Master is a server and DB supporting no applications (hence no OLTP). Would latency be the same/better/worse? Should we stick with our current implementation and just performance tune it?A secondary question I have is given the bidirectional replication options above did we choose the best one for us? These servers are all on the same network hosted by the same provider over Gigabit Ethernet (I assume). I think we have the polling interval set at 5 seconds and we are thinking of moving it to 10 seconds at most. Real-time latency is not critical to our business but it would be a "nice to have". For conflict resolution we are keeping it simple, whichever was inserted/updated last "wins". It looks like Bi-directional Transactional replication might be a better option for us. Would it give us the autonomy we are looking for? Any major "cons" to using Bi-directional Transactional replication over merge replication (beside scalability). Scalability may come into play a few years down the road but for now it is not a high priority. Also would the Master model described above using Bi-directional Transactional replication be a successful implementation?ETA - One thing merge replication gives us is autonomy between our application servers, particularly when ServerA needs to come down for upgrades, the applications on ServerB can still function without any dependencies like we had before with 1-way transactional replication with linked server calls.

View 2 Replies View Related

Meta Integration Solutions

Apr 27, 2007

Hi,



Has anyone experience with Meta Integration Solutions and converting to SSIS ?

and converting to SQL ?

and converting to Analysis Server ?

and converting to Reporting Server ?



You can find their website http://www.metaintegration.net/Products/Overview/Solutions.html



Constantijn Enders



View 1 Replies View Related

Tranferring/Copying Entire Database To Remote Server

Jul 15, 2007

This problem may appear trivial to you guys but is troubling me since quite some time now! The problem is that I created a website using express studio happily and it worked flawlessly on the local host. Now I want to move it to a remote server and my host created a database name ASPNETDB for me on the server. The problem is that that ASPNETDB database is virtually empty and I want to copy all my tables,stored procedures etc etc(complete database) to the remote ASPNETDB database. How can I do that? In the management studio express edition there is no command available to copy paste all contents of the database to another database. Please help I am quite confused!

View 4 Replies View Related

SQL Northwind End To End Database Solutions (examples)

Apr 30, 2004

Does anyone know where I can find a Northwind end to end database solutions (examples) written in ASP.NET (VB). I would like to reverse engineer this project to learn more about ASP.NET?

Thanks.

View 1 Replies View Related

Issues For Embedded SSIS Solutions

Mar 16, 2006

My warehouse app employs a distributed architecture. Extractions from disparate (wildly so) systems, and transformations and loads into a standardized schema are performed at various locations close to the source systems (both physically and "logically" speaking). There are security and other reasons for this. However this causes some related design and implementation challenges for the ETL processing.

For one, the ETL processes must be successfully operated by non-technical medical administrators, who actually have little interest in the application and sometimes even the analytics produced by the system, who have other more pressing day to day work they want to be doing, in organizations where turnover is high,training is spotty, and LANs are fragile and often congested.

So, real-time feedback to the operator during processing is pretty dern important. I have built a fairly sophisticated GUI (using .Net forms inside a script component) for the operational interface-input boxes just wouldn't cut it).

But that interface is lacking real time feedback as to processing progress at runtime.

Anyone got that T-shirt yet? I'm thinking I need progress bars and real-time task and component progress reports. Also. is there a way to capture the built in logging output in real-time?

View 5 Replies View Related

Solutions To Large Access In SQL 2005

Dec 17, 2007

I am on a project to develope an route finding system that search for the optimal route to stick with for users of the system. The current version that i've done and successfully run is using normal database access in MS SQL 2005. I stored nodes information in the database and the application will query them using normal "select" clauses and return a datatable object to the application. The result is rather slow cause by the multiple access to database server to query. The application used 8 second to look for a short route withour cosidering lots of calculation of traffic information that i will use later. Any comments on the architecture or approach to switch my algo to T-SQl?

View 5 Replies View Related

How Can I Copy The Entire Database From One Sql Server To Another Sql Server?

Feb 11, 2004

How can I copy the entire database (all the objects from one database to another database)?

thanks in advance.

View 2 Replies View Related

Need Links For Multiple Keyword Search Solutions Please

Jun 6, 2008

I have been informed that all my keyword search solutions are susceptible to SQL injection attacks.  Does anyone have links discussing basic ' multiple ' keyword search solutions?  I would think this is a very common routine (perhaps so much so than only newbies like myself do not know it).  I have read the posts about escaping ', doing replace " ' ", " '' ", using parameters and yet every multiple keyword solution I come up with is said to be injection prone.
Example: visitor enters:  Tom's antiquesinto a TextBox control and the C# code behind securely generates the below call to the database.
SELECT L_Name, L_City, L_State, L_Display FROM tblCompanies WHERE L_Kwords LIKE '%' + 'Tom's' + '%' AND L_Kwords LIKE '%' + 'antiques' + '%' AND L_Display = 1 RETURN
I understand that concantenting string parts using an array and then passing the sewn together string to a stored procedure exposes it to injection.  I hope that my single keyword routine below is secure, if it is not then I am not understanding how parameterized SP are supposed to be constructed to protect against injection.string CompanyName;CompanyName = TextBox1.Text;PROCEDURE CoNameSearch @CompanyName varchar(100)AS SELECT DISTINCT L_Name, L_Phone, L_City, L_State, L_Zip, L_Enabled, L_Display FROM tblLinksWHERE (L_Name LIKE @CompanyName + '%') AND L_Enabled = 1 AND L_Display = 1 ORDER BY L_NameRETURN
 

View 5 Replies View Related

What Solutions Make Returning From A Disaster Easier?

Apr 18, 2007

I'm trying to figure out what solution (replication, mirroring, clustering) would work best for me.

I have been reading many articles in BOL and in this forum. Most talk about getting data TO a backup/standby/subscriber, but I can't find a lot of info regarding getting the data BACK after a disaster is over.

We have a main office and a disaster recovery facility. Most of the time there are no data updates at the disaster location. So, I need to get data to the disaster facility via WAN (latency is not a huge issue - end of day syncing is fine) for backup purposes. In the event of a disaster, the main office will be offline and data changes will happen at the disaster site. When the disaster is "over" and we return to the main office, what's the best scheme to reverse the data back to the main office to start business again? We are a financial company, and have gigabytes of relatively static data. Most changes are current day. So, to snapshot a 100GB database when I know only a few hundred MB changes a day doesn't seem feasible to me.

Most replication scenarios (at least from what I see) can't easily "reverse" the replication after a disaster situation. I'm looking at merge replication on a schedule which seems to look good, but was wondering if anyone else has any ideas or suggestions?

View 5 Replies View Related

Best Practice For Report Projects Related To Application Solutions

Feb 20, 2007

Hello Reporting Services Gurus!

I'm about to start on my first reporting services project, but before I mess it up, I'm looking for some guidance on how best to achieve my mission. Here's what I'm looking to achieve:

I have a datacentric application (SQL Server 2005 Express w/ Advanced Services backend) in which I want to build about 50 "canned" reports for the end users. I want to build the reports utilizing server mode so I can take advantage of some of Reporting Services advanced features. I'm not sure what the best practice would be to build the reporting services project. Is it better to include the report project as another project within the application solution? Or, should I build the report project independent of the application solution? What are the pros and cons of doing it either way? How does including the report project build if it's included in the application solution? How would a ClickOnce deployment deploy the report project to the report server?

My ultimate goal would be to have an "off-the-shelf" software solution that includes an installation package consisting of the application project and report project. Is it even possible due to the Reporting Services architecture to achieve an install in this manner with ClickOnce, Windows Installer, or Installshield? Or, is building the report project indepedent of the application project and deploying the reports to the report server "manually" (i.e. deploy within the report server project) the only solution?

Any help would be greatly appreciated!

Tony

View 1 Replies View Related

MS SQL Server Is Good With ...?

Feb 13, 2004

Hi,

I'm about 6 weeks into SQL and SQL Server (7) - I was wondering whether you could share your opinions about which language to use as a programming tool for developing apps for & with SQL Server. I'm choosing between C++ (Visual) or JAVA.

I already know C and the DB-Libe contains a lot of it but I'm kinda trying to expand some horizons. I'm ok with either C++/VC++ or JAVA but I only have time to learn (or be good at) one.

Any suggestions? (I'd like to hear what you think even if you say neither C++ or JAVA - maybe VB? What's easy and marketable is what matters most.)

Thanks.

View 1 Replies View Related

To Have No FAT Partition - Good Or Bad For Server?

Jan 21, 1999

Hi!
I'm installing a new SQL Server machine. During NT Server
installation our NT support guy converted the only 2GB FAT
C: partition to NTFS. So as of right now all my 4 8GB drives are
NTFS. I think it would be better to keep this C: partition in FAT
because, as of my knowledge, having FAT boot partition can help
to boot the machine in case of NT crash.

Is there anything that I'm really losing by this conversion to NTFS or I
should not be worried so much about it? Does it put my SQL Server
databases, database .dat files or NT Server in more danger situation
in case of any crash?
Or it's giving me some advantages?
Thanks
Ninel

View 2 Replies View Related

Good SQL Server Books

Sep 26, 2006

Can anyone make some good book recommendations for SQLServer2000 other than the Microsoft BOL?
Thanks

View 11 Replies View Related







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