How do I install a Microsoft SQL Server 2005 database application in an existing server in an existing database server and still have the control over it and also restrict the new server Admin user from editing or even opening my DB. Any modification of my database or code should be implemented only by me. Is it possible to remove the Builtin Admin from the server role?? As in my case, there is no need for anyone else to open the DB in Management Studio at all as my VB application does all that is required.
I've been parachuted into a place with a few dozen servers, several thousand external users (and scheduled to grow). Right now the whole place - IT and non-IT alike - l logs in with SA.....
(where's the 'Puking' icon??)
They've got Windows logins, but that's about as tight as it gets. Honestly I've never seen anywhere as insecure....and not sure where to start.
i would like to pose a question, when a user do a login in SSRS server,is it possible to pass the username to SSAS tabular, to implement security? I've tested using USERNAME() creating a role that relates USERNAME() with a dim user username, but now i need to implement it with the username used in SSRS server.
What is the best way to implement security in a SQL database to reduce the loss in the situation when a database is stolen?
In my tests for disaster recovery while catering for security I am using
create master key with password, create certificate and then symmetric key using certificate.
This does require the master database password to be known if restoring on a second machine before anything can be recovered. But is there anyway to secure this even more?
If things like passwords and stuff needs to be specified then it would require an application change as well, at the moment the changes are made to be seamless for applications (inserts/updates done via triggers until the app is made aware of the schema changes) so that is viable but not for the first phase implementation.
Basically looking for an idea of is this is considered a good enough start, and what possible changes can be made to ensure data is even more secure later (algorithm used is AES128 and AES256), data will be emails, creditcard details (so MasterCard/VISA compliancy needs to be adhered to as well), etc.
I must prepare a procedure which should export some data from some DB columns, but these data must be encrypted using the OpenSSL protocol, to be sent to an external recipient.This recipient sent to me a "public" certificate in PEM format, to what I understood (it is a file beginning with the expression "-----BEGIN CERTIFICATE-----", then a long Base64 sequence and then "-----END CERTIFICATE-----").
Then I didn't create the certificate nor I am a certification authority: I just have this public key (or, at least, a file from which I can extract a public key) and I must use it to encrypt data in order to obtain an output which must be analogue to what I can obtain when using this OpenSSL command line instruction:openssl rsautl -encrypt -in CF.txt -out CF.enc -inkey SaniteICF.cer -certin -pkcs
Then the question is: starting from this PEM file and nothing else, can I import it and store an asymmetric key or a certificate in SQL Server and use it to encrypt data to be exported?
I want to use an Active Directory security group that is a Distribution List for a new role assignment for an existing report. Can someone tell me if this is possible? I get an error each time I try:
The user or group name <DLName> is not recognized. (rsUnknownUserName)"
I wish to do a major change on our security profile. Basically, I wish to allow a group of users to do everything but modify objects/change logins/drop-create objects. Is there a quick way to build and apply this model ?
Can I implement the Standby SQL Server using the SQL Server Standard Edition? What's actually the difference between Standard and Enterprise Edition? As I know the Enterprise edition can be installed for up to 32 CPU while Standard edition can only be installed for up to 4 CPU.
I want to be able to implement infinite levels of Hierarchies in SQL Server (2012), in addition to being able to address issues like same child having more than one parent (eg. An Employee could end up having 2 different managers - eg. Project Manager, Delivery Manager).
One way is to have self referencing table (where each row has a parent id , referencing to a parent record - but this would not work in cases where a child has more than 1 parent).
Are there other more efficient ways? What is the best way to implement this?
I'm a student of computer science, and for one of my projects I would need Weka plug-in for SQL Server 2005. I don't know much about plug-ins, so if I'm asking stupid questions, I hope you will forgive me. It was easy to get a library from weka that I can use in Visual Studio, but I just can't figure out, how to get Weka data mining algorithms into plug-ins for MSSQL 2005.
I am implementing a message queue system in SQL Server 2000. My queue table looks something like this:
[MessageId] [uniqueidentifier] NOT NULL, [MessageType] [uniqueidentifier] NOT NULL, [Status] [tinyint] NOT NULL, [SubmittedTime] [datetime] NOT NULL, [StartTime] [datetime] NOT NULL, [DispatchedTime] [datetime] NULL, [CompletedTime] [datetime] NULL, [MessageData] [image] NULL
This is how I retrieve the next message for processing:
SELECT TOP 1 * FROM [Queue].[MessageQueue] WITH (ROWLOCK, UPDLOCK, READPAST) WHERE [StartTime]=@pStartTime AND [MessageType]=@pMessageType AND [Status]=@pStatus ORDER BY [StartTime]
and mark it as being processed:
UPDATE [Queue].[MessageQueue] SET [Status]=1 WHERE [MessageId]=@pMessageId
After message has been processed I delete it from the queue:
DELETE FROM [Queue].[MessageQueue] WHERE [MessageId]=@pMessageId
All database accesses are transactional with default READ COMMITTED. The problems start when there are a few concurrent accesses: I get deadlocks when retrieving next message. If I do not delete message after processing then there is no deadlock. But this is not what I need.
I played with different isolation levels and locking hints and was able to avoid deadlock using TABLOCKX:
SELECT .... FROM [Queue].[MessageQueue] WITH (TABLOCKX)
But in this case you cannot concurrently retrieve messages which was my goal from the beginning. How do I achieve this?
How can I implementing Membership and Role Management on SQL Server 2005 (not Express)? Anyone have any documentation? Are there any scripts to run that will basically set up the same Database schema on SQL Server 2005 that Express uses? Thanks.
Hello Folks I am a total new boy to Visual Web Developer and am struggling to implement a lookup field in the SQL Server Express that ships with it. I am trying to find the equivelent of using the lookup wizard in MS Access. So in Access I can select the lookup wizard from the drop down list of datatypes and refer to another table of standing data. Once configured the tables contents appear as a drop down list in the field of the master table. So how do I create a table in SQL Server 2005 that uses another table to give a drop down list in one of its fields? I have found some excellent tutorials and books but none of them seem to drill down to this level of table design, can anyone recommend any books that do? Thanks Bradley
I am looking for some assistance from the grand knowledgebase out there concerning the implementation of Sql Server Express 2005 on a client's strict domain environment.
I am designing and implementing a pos software that resides on registers and a server within a number of stores. The registers are running WePos and the server is running Server 2003. I run an instance of sql server express on all devices. The registers read the server when it can see it but when it cannot it reads the local instance. I am seeing a number of performance issues and I am trying to tweek the installation and coding of SSE on all devices.
Microsoft recommends using Windows authentication instead of SQL Server authentication in SQL Server 2005 for improved security. What are the Microsoft best practices for implementing this? Will be helpful if someone also provides some links that talks about this....
I went to look at the connection string previously entered for a dataset created in a new report, and am not seeing anything intuitive for bringing up the associated datasource dialog box that was used to enter name, type and connection string. I'm also noticing nothing intuitive for deleting an existing dataset. How do you do these two very simple things in an existing project? I dont see the dataset in solution explorer, I see it only in the text box on the data tab and in a limited kind of way on the dataset view where the columns show and maint is allowed mostly on the columns only. I tried hilighting the dataset here and hitting the delete key to no avail.
I would like to restore database using RESTORE DATABASE ... REPLACE command. If database exists already and has any open connections this command will fail. I would like to close all existing connections to specific database before running RESTORE DATABASE ... REPLACE command. I can do closing from Management Studio using checkbox "Close Existing Connection" when deleting database. Actually I need to do the same but from script.
Hi y'all I think i have some problem in here perhaps you guys can help.I have this code: 1 public void AddQstrWhere(ref string TmpStr,string Parameter) 2 { 3 if(TmpStr=="") 4 { 5 TmpStr +="WHERE"+Parameter; 6 } 7 else 8 { 9 TmpStr +="AND"+Parameter; 10 } 11 } 12 13 14 public string querySlsPerson; 15 //public string queryLastSls; 16 public ArrayList CLSGetSalesman(string szSalesmanID) 17 { 18 19 20 // TODO: Add CLSReportManager.CLSGetSalesman implementation 21 BOSTx Tex = GetTx(CLSReportConstants.TrnMasterID.FQN2,"QueryRptSales"); 22 try 23 { 24 string querySalesPerson = "SELECT dbo.BOS_GL_Workplace.WpszState, BOS_PI_Employee_1.szName, dbo.BOS_PI_Employee.szName AS Expr1, dbo.BOS_PI_Employee.szEmployeeId, "+ 25 " dbo.BOS_PI_Division.szName AS Divisi, dbo.BOS_PI_Team.szDescription "+ 26 " FROM dbo.BOS_PI_Employee LEFT OUTER JOIN "+ 27 " dbo.BOS_GL_Workplace ON dbo.BOS_PI_Employee.szWorkplaceId = dbo.BOS_GL_Workplace.szWorkplaceId LEFT OUTER JOIN "+ 28 " dbo.BOS_PI_Division ON dbo.BOS_PI_Employee.szDivisionId = dbo.BOS_PI_Division.szDivisionId LEFT OUTER JOIN "+ 29 " dbo.BOS_PI_Team ON dbo.BOS_PI_Employee.szTeamId = dbo.BOS_PI_Team.szTeamId LEFT OUTER JOIN "+ 30 " dbo.BOS_PI_Employee BOS_PI_Employee_1 ON dbo.BOS_PI_Employee.szSupervisorId = BOS_PI_Employee_1.szEmployeeId "; 31 //"WHERE dbo.BOS_PI_Employee.szEmployeeId = '"+szSalesmanID+"'"; 32 //" WHERE BOS_GL_Workplace.WpszState = '" + szProvince + "' AND BOS_PI_Employee.szName = '" + szsalesPrsn + "' AND BOS_PI_Employee_1.szName = '" + szSupervisor + "'"; 33 34 string wheretext = " dbo.BOS_PI_Employee.szEmployeeId = '"+szSalesmanID+"'"; 35 string queryLastSls; 36 //AddQstrWhere(szSalesmanID,wheretext); 37 if(szSalesmanID==string.Empty) 38 { 39 queryLastSls = querySalesPerson; 40 } 41 else 42 { 43 //string querySlsPerson; 44 AddQstrWhere(ref queryLastSls,wheretext); 45 46 queryLastSls = querySalesPerson + querySlsPerson; 47 } 48 49 OleDbCommand comd = Tex.GetTextCommand(); 50 comd.CommandText = queryLastSls; 51 52 DataTable slTable = new DataTable(); 53 OleDbDataAdapter adapt = new OleDbDataAdapter(comd); 54 adapt.Fill(slTable); 55 56 57 ArrayList DataSalesArr = new ArrayList(); 58 foreach(DataRow row in slTable.Rows) 59 { 60 CSDatasales sls = new CSDatasales(); 61 sls.sz_province = row["WpszState"].ToString(); 62 sls.sz_supervisorname = row["szName"].ToString(); 63 sls.sz_salemanname = row["Expr1"].ToString(); 64 sls.sz_salesmancode = row["szEmployeeId"].ToString(); 65 sls.sz_Divisi = row["Divisi"].ToString(); 66 sls.sz_typeOp = row["szDescription"].ToString(); 67 68 DataSalesArr.Add(sls); 69 } 70 Tex.CloseConnection(); 71 //AddQstrWhere("",wheretext); 72 73 return (DataSalesArr); 74 75 } 76 catch(Exception ex) 77 { 78 throw ex; 79 } 80 81 } 82 You see I want to make the query more dynamic. So instead of making the SQL parameter permanent I wanna make it dynamic, so the usercan choose whether they want to use the parameter or not. I already declare the parameter which is szSalesmanID. There's a function called AddQStrWhere which can make the query more dynamic. But the thing is when I wanna use the parameter it seems the code doesn't take it, it just use the regular query I dont know why.I already use the function but it just wont work, can you guys tell me what's wrong, Please I really do need some help from you guys, feelin; a lil' bit desperate in here.I appreciate any kinds of help, thanks. FYI: I use Visual Studio.NET 2003, SQL Server 2000.Best Regards.
How to Implement scd type 1 and 2 functionality using DTS. Implemented the functionality using T-SQLS, but want to make use of DTS. Can we do this using DTS without using T-SQLs.
---------------------------- News ---------------------------- ID | Headline | Article ----------------------------
I want the user to be able to search for keywords in articles. Here's my stored procedure:
CREATE PROCEDURE sproc_GetNewsSearch ( @keyword varchar(50) ) AS SELECT Headline, Article FROM News WHERE Article LIKE '%' + @keyword + '%'
This will work fine if I search with one word. If I enter "zebra", it will find the articles containing "zebra".
The problem is when I search for more than one word. If I enter "monkey zebra", it won't search for articles containing "monkey" and "zebra", it will look for "monkey zebra".
I have about a half-dozen insertion tasks that depend on a condition: SQLServerProcessDate == OLEDBprocessDate. If the task executes early, old data gets inserted and needs to be backed out later on. (This happens about two-three times a month.)
The jobs start at 8:00 PM, but on occasion processing runs late. In order to make the task intelligent enough to not insert old data, I've thought of a couple options:
1.) Separate scheduled job runs continuously, comparing process dates. When the dates match, it sends a message to the other jobs. Pro: less repetitive. Con: another scheduled job?!? I'll have to learn the message queue task; a little bit complex; flood of database activity on message reception may slow system down
2.) Compare the dates in each individual task; if they're not the same, sleep for a while (15 min.) Pro: simpler. Con: I haven't found any way to pause/sleep/wait a task based on a condition; repetitive functionality in multiple tasks
I am writing my own SSIS package host and I want to create custom logging and event trapping in the host. My goal is to reroute logging calls to the patterns and practices logging block and control the logging destinations using the config file.
Here is an excerpt of code from my main method of the host:
// recurse through all DtsContainers in package //and enable logging, set filter kind to Exclusive, and clear filter list ConfigureLogging(package);
// LoggingEventHandler inherits from Microsoft.SqlServer.Dts.Runtime.DefaultEvents LoggingEventHandler eventHandler = new LoggingEventHandler();
// LoggingBlockLogger implements IDTSLogging LoggingBlockLogger logger = new LoggingBlockLogger(); DTSResult result = package.Execute(null, null, eventHandler, logger, null);
The behavior I am looking for is that regardless of what logging providers are configured in the package, all the providers will be bypassed and all logging will be passed through my custom LoggingBlockLogger class. However, I find that if there were any providers configured in the package, they are being passed logging events. I tried returning 'false' from LoggingBlockLogger.Enabled, and I'm returning an empty array from LoggingBlockLogger.GetFilterStatus().
I followed the stack trace from the point of entry into the package log providers and found that they were being called directly from ManagedWrapper.Log. Why are the package providers still being called? What is the purpose of the IDTSLogging interface if every container simply passes log entries to the providers in its LoggingOptions.SelectedProviders collection?
We're trying to implement an audit mechanism to track all changes made to our database and would appreciate any suggestions or alternate ways to do this, we're using the regular trigger based approach. We also need to capture the user name of the current logged in user - this is NOT the sql server database user, it is at the application level, the user who logs into our site. We were planning to pass this username using context_info in SQL SERVER through triggers as described here - http://www.sqlservercentral.com/columnists/spustovit/easyauditingasharedaccount.asp Alternatively I could add a userId field to every BC and store the logged in user's name there but I'd rather avoid that.
I'm not trying to do anything too fancy; given a string, I just wanna see if anything in the column of my database matches the string. I'm using an SQL query that takes a string, then selects the data using LIKE %searchword%.This works fine when the user enters only one word. But I guess you can see that a problem arises when they enter more than one word.So how can I implement a very simple search that will take more than one word?
Hi there, I am a little confused with some data ideas in .NET 2.0. First, I now understand there are profiles for storing per-user information (such as address, etc). Now, each of my users will have say an inventory of equipment, that they can edit, add, etc. However I am not sure if using just regular tables would be better. Any suggestions? Also, I understand using the profiles does not let you use things such as the DetailsView control, which would allow for automatic editing, adding, deletion, etc. This would be very nice to have, rather than implementing it myself. If this is the case, how do I associate the new database with the ASPNETDB database? For example I will have a table in my new database that has columns [UserId, EquipmentName, Quantity, Description]. Now how do I get the logged in user's user ID to display only their equipment in a GridView say? Thank you very much! Tristan
I am working on a project where I want to implement service broker. A lot of the examples that I have seen are all based on T-SQL. Here are my questions.
1. I have a web page that allows a user to type in an order. How do I put a message on the queue using a .NET assembly? Is there some sort of API set? Do I just pass the message to a stored procedure that puts the message on a queue?
2. Are there any samples that illustrate how to read messages off a queue using a console application or windows service? Is it possible to read the messages off of the queue with a console application or do you have to subscribe to the queue activation event and write a service program that runs under the control of service broker? All of the data that I need to fill the order has to come from a DB2 database on the mainframe. Therefore, I am stuck with creating an external application that processes the messages.
3. In order to use Service Broker, do I have to install Notification Services as well?
Can anybody tell me the advantage and disadvantage to use NT security for SQL Server 7.0? For a corporation with 400 users, what is your recommendation for the SQL Server security management. Thanks.
I am working on one application, which retrieves data from multiple tables in the database and all the fields retrieved, are exported as an excel sheet. All the export functionality is done through DTS. And the data is retrieved using an SP.
I am supposed to use an "incremental backup" approach here. Means, for the 1st scheduling of the package, all the database dump will be there in excel file. But, after that only fields that are updated/inserted are to be filled in the excel file. Each time, when the dump of the database is taken, the excel file is stored in an archive folder with Date and tiemstamp(e.g. TEST_11_04_2007_15_00_00.xls)
e.g. For the first dump, I get 100 records from the database. Before next execution of the package, 10 rows get inserted and 1 row gets updated. So, on the second time execution of the package, I should populate the excel sheet with those 11 rows only, and not 110 records.
I am not authorised to change the database schema. So, is there any approach to try out this?
Hi,I would like to use database locking mechanism to control access to anexternal resource (like file system).What I need is1. an exclusive (write) lock conflicting with any access to theresource (both for read and write)2. non-exlusive (read) lock conflicting with writes onlyHow this could be done?I'd appreciate any reply.Vadim
I am trying to setup a linked server on 2 machines. Both machines have identical SA login/pwd. Also, both machines have a database called Federated_Bridge.
The setup I have is as follows:
Machine 1: (local) - As it appears in Enterprise Manager Machine 2: (RealIBM2) - As it appears in Enterprise Manager
Earlier I executed a query to successfully link machine 1 to machine 2 as follows:
The above query works. However, when I try to do the same thing from Machine 2, it's not working. It doesn't seem to like the (local) name for the @datasrc field. Here is my new query .. which is failing:
Code Snippet Public Function GetView() As ContainerControl _ Implements IDtsTaskUI.GetView
Return New CustomTaskForm(Me.taskHost, Me.connections)
End Function
When i try to drag the task to a new Package i get the following error:
TITLE: Microsoft Visual Studio ------------------------------ Failed to create the task. ------------------------------ ADDITIONAL INFORMATION: The task user interface specified by type name 'CustomTaskExampleUI,Version=1.0.0.0,Culture=Neutral,PublicKeyToken=b853fe59589b971f' could not be loaded. (Microsoft.DataTransformationServices.Design) For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%u00ae+Visual+Studio%u00ae+2005&ProdVer=8.0.50727.762&EvtSrc=Microsoft.DataTransformationServices.Design.SR&EvtID=CouldNotLoadTaskUIForType&LinkId=20476 ------------------------------ The given assembly name or codebase was invalid. (Exception from HRESULT: 0x80131047) (mscorlib) ------------------------------ BUTTONS: OK ------------------------------
I need to know whether changing/migrating process in SP to SSIS is a good idea to enhance performance in datawarehouse. My client want to do this because they have problems in performance while executing SP from dts 2000 packages. So they don't want to use SP anymore. They want to use all SSIS features. Since I'm newby with SSIS, I don't have any idea how to implement SPs in SSIS, especially while working with temporary tables. Btw, this is snippet of the SP I want to change :
ALTER PROCEDURE [dbo].[DBAS_BBCBG_DataDaily] @CustomDate datetime = null AS BEGIN -- Begin Procedure- --15 Minute DECLARE @date datetime SET @date = isnull(@CustomDate, (SELECT top 1 Reporting_Date From DBDS..DataDate)) SELECT * INTO #TbDataDaily FROM DBABC.dbo.TbDataDaily WHERE 1=2
BEGIN INSERT INTO #TbDataDaily ( Col1, Col2, Col3, Col4, ValDate ) SELECT CD1,CD2,CD3, CD4, @date FROM DBAS..HDKFS CREATE INDEX IDX_ABC_DataDaily ON #ABC_DataDaily (Col3,Col4) ON [PRIMARY] END
BEGIN UPDATE #TbDataDaily SET Col2 = B.CABLVT FROM #TbDataDaily A, DBAS..COLACBFP B WHERE A.Col3 = B.CAKNTN END
... and so on...
all other processes in the SP are just about to SELECT/INSERT/UPDATE temporary table, and finally fill the data to physical table.
So how can I change this SP to SSIS package? (of course if this is the best way to improve performance)
