SQL Security Related
Aug 11, 2005
We had been running SQL Server without any control of security (since
the company is very small -100 employees). All of us know the admin
password and has been accessing the database as admin. Our database
server crashed due to hardware failure twice last month and we lost a
lot of important data. Now the management is taking the control of
server access seriously.
SQL Enterprise manager is installed on many PCs and any one can delete
any database with a right click.
My question is:
1. Can the enterprise manager be installed on client's PC with a
limited right (or as a user not as admin)?
We need to limit the user's access of using the Enterprise Manager.
In other words, how can we set this up for different users.
2. How can we keep running SQL Server if one server fails?
Clustering or Replication or Mirroring? O
I would highly appreciate if you could direct me to any website or
resources on how to set up security of SQL Server (2000 with the latest
service pack).
Thanks a million in advance.
Best regards,
Mamun
View 2 Replies
ADVERTISEMENT
Jul 23, 2005
On the other database types, there is an audit capability in that yourecord such items asfailed login attemptsattemtped access to tables user is not authroized tochanges to databse schemachanges to permissionschanges to logins (add, delete, lock, unlock, passwrod reset)All I can find in the SQLServer documentation is the reference totracking failed logins when you set up a database, plus the Profiler'sactivities.Yes, I'm taking voer my first SQLServer database and have been asked tomake sure that this database is closely monitored for inappripriateactivity.Questions:1) Does SQLServer have this capability? (Sybase has this, which iswhere I'm coming from)2) Does SQLServer do this automatically or do I have to set up theevents to be tracked as happens with Sybase?3) What commands are there for setting up these events to be tracked?Thanks in advance!
View 2 Replies
View Related
Aug 1, 2006
i am testing some encryption
scenarios ,in profiler the statements like "OPEN KEY" and all "Encrypt"
and "Decrypt" functions are removed automaticly from the trace
and replaced with a comment ,create a trace and try the code i attached ,
you will see in profiler trace that that all encryption related commands
are commented out ,this is what expected.
but now go to the batch and comment out the "SELECT @rrr' statment,
and run the batch ,this batch will fail beacuse "@rrr" is not declared,
now go back to profiler and you will see that for the failed batch
all the encryption command are NOT COMMENTED OUT !!!
esspecially important is the visibility of the password of the open key command.
seems like a very dangerous bug to me!!!
CREATE CERTIFICATE test1
ENCRYPTION BY PASSWORD = 'pGFD4bb925DGvbd2439587y'
WITH SUBJECT = 'Sammamish Shipping Records',
EXPIRY_DATE = '10/31/2009';
GO
CREATE SYMMETRIC KEY Key09 WITH ALGORITHM = TRIPLE_DES
ENCRYPTION BY CERTIFICATE test1;
GO
declare @Str nvarchar(100)
declare @Enc varbinary(max)
set @Str = 'encrypt this'
OPEN SYMMETRIC KEY Key09
decryption by CERTIFICATE test1 WITH PASSWORD = 'pGFD4bb925DGvbd2439587y'
SET @Enc
= EncryptByKey(Key_GUID('Key09'), @Str);
---select @rrr
select CONVERT(nvarchar(100), DecryptByKey(@Enc))
go
View 1 Replies
View Related
Oct 26, 2007
CLIENT SIDE:
If the query is reading from large table, (100 columns x 20000 rows)
I have no problem getting results using SQL Query Analyzer on the Client side.
However, I am getting timeout problem from the client side application.
The query failed. The message from the database engine was:
Microsoft OLE DB Provider for SQL Server: Timeout expired.
SERVER SIDE:
I tested the same query on the server using the application. I can get the results.
ENVIRONMENT:
Server machine:
The Server : Windows 2003 Server SP2
Database Server : SQL Server 2000 €“ (8.00.2039 Standard Edition SP 2)
linkserver (OLE DB 9.0.0.3504 ) to FoxPro 9.0 SP1 table
SQL Server Timeout Settings: Query time-out (sec, 0=unlimited)
Client machine:
Windows XP SP2 : Windows Network Authentication
SQL Server 2000 client
For some reason my environment doesn€™t like the outside application to connect to the server long time?
Do you have any idea how to fix this timeout problem? Do I need to configure DCOM or DTC?
View 1 Replies
View Related
Mar 20, 2008
Hi thanks for looking at my question
Using sqlServer management studio 2005
My Tables are something like this:
--Table 1 "Employee"
CREATE TABLE [MyCompany].[Employee](
[EmployeeGID] [int] IDENTITY(1,1) NOT NULL,
[BranchFID] [int] NOT NULL,
[FirstName] [varchar](50) NOT NULL,
[MiddleName] [varchar](50) NOT NULL,
[LastName] [varchar](50) NOT NULL,
CONSTRAINT [PK_Employee] PRIMARY KEY CLUSTERED
(
[EmployeeGID]
)
GO
ALTER TABLE [MyCompany].[Employee]
WITH CHECK ADD CONSTRAINT [FK_Employee_BranchFID]
FOREIGN KEY([BranchFID])
REFERENCES [myCompany].[Branch] ([BranchGID])
GO
ALTER TABLE [MyCompany].[Employee] CHECK CONSTRAINT [FK_Employee_BranchFID]
-- Table 2 "Branch"
CREATE TABLE [Mycompany].[Branch](
[BranchGID] [int] IDENTITY(1,1) NOT NULL,
[BranchName] [varchar](50) NOT NULL,
[City] [varchar](50) NOT NULL,
[ManagerFID] [int] NOT NULL,
CONSTRAINT [PK_Branch] PRIMARY KEY CLUSTERED
(
[BranchGID]
)
GO
ALTER TABLE [MyCompany].[Branch]
WITH CHECK ADD CONSTRAINT [FK_Branch_ManagerFID]
FOREIGN KEY([ManagerFID])
REFERENCES [MyCompany].[Employee] ([EmployeeGID])
GO
ALTER TABLE [MyCompany].[Branch]
CHECK CONSTRAINT [FK_Branch_ManagerFID]
--Foreign IDs = FID
--generated IDs = GID
Then I try a simple single row DELETE
DELETE FROM MyCompany.Employee
WHERE EmployeeGID= 39
Well this might look like a very basic error:
I get this Error after trying to delete something from Table €œEmployee€?
The DELETE statement conflicted with the
REFERENCE constraint "FK_Branch_ManagerFID".
The conflict occurred in database "MyDatabase",
table "myCompany.Branch", column 'ManagerFID'.
Yes what I€™ve been doing is to deactivate the foreign key constraint, in both tables when performing these kinds of operations, same thing if I try to delete a €œBranch€? entry, basically each entry in €œbranch€? and €œEmployee€? is child of each other which makes things more complicated.
My question is, is there a simple way to overcome this obstacle without having to deactivate the foreign key constraints every time or a good way to prevent this from happening in the first place? Is this when I have to use €œON DELETE CASCADE€? or something?
Thanks
View 8 Replies
View Related
Dec 4, 2014
I use from sql server 2008. and c#
what is the best connectionstring?
I don't know if i use Persist Security Info and Integrated Security or not?
And if yes then their value must be true or false?
View 1 Replies
View Related
Oct 14, 2005
Hello there I have trying to figure out for days how to enable FullTrust for my Reporting Services security extension.
View 9 Replies
View Related
Jul 31, 2007
Hi,
I have posted this issue for a week, haven't got any reply yet, I posted it again and desperately need your help.
The article http://msdn2.microsoft.com/en-us/library/ms365343.aspx says:
Model Item Security can be set for differnt security filters, but when I use SQL Server Management Studio to set Model Item Security, it seems "Permissions" property surpass "Model Item Security" property. -- My report server is using Custom Authentication.
For example, in "Permissions" property of the model, if I checked "Use these roles for each group or user account" without setting any user or group, no matter what users I added to "Model Item Security" with "Secure individual model items independently for this model" checked, NO one user can see the model on report manager and report builder;
in above situation, if I added "user1" and gave role such as "Browser" role to "user1" in "Permissions" property, if I checked "Secure individual model items independently for this model" in "Model Item Security" property, even I did NOT grant "user1" to root model and any entities under the model, the "user1" is able to access the model and all entities in report builder.
My question is on the same report model, how to set "AdminFilter" (empty security filter) for administrator permissions and set "GeneralFilter" (filtered on UserID) for general user based on their UserID?
The article also says:
"Security filters are always applied, even for users who have Content Manager or Administrator permissions to the model. To allow administrators or other users to see all rows of an entity on which row-level security is defined, you can create an empty security filter (which always returns True) and then use the filter to grant those users access to all the rows."
So I defined 2 filters "GeneralFilter" and "AdminFilter" for "Staff" entity for my report model "SSRSModel", I expect after I deployed the report model, the administrator users use report builder to build reports with all rows available, and the non-admin users can only see rows based on their UserID.
I can only get one result at a time but not both:
either the rows are filtered or not filtered at all, no matter how I set the "SecurityFilter" for the entity: I tried setting both "AdminFilter" and "GeneralFilter" for SecurityFilter at the same time, combination of "DefaultSecurityFilter" and "SecurityFilter", or one at a time.
Your help is highly appreciated!
Desperate developer
View 1 Replies
View Related
Apr 26, 2007
hi i want to know what is the differance between
Persist Security Info=False;Integrated Security=Yes;
View 1 Replies
View Related
Oct 18, 2015
Is there any possibility to schedule SQL job execution as Windows Security Group? I need to run powershell script through SQL job with one of this group member's permissions.Â
View 4 Replies
View Related
Jul 6, 2007
I have Sql Server Express installed on Vista (service pack 2)
I have Visual Studio 2005 with an application that I'm trying to access it with within a WCF service.
The login ID of the service is added to the database.
The database has remote access turned on.
The ID is granted access to all databases within the server.
The thread is being set with WindowsProvider and the services set their thread to WindowsProvider.
The dataserver is set with using Windows Authentication for security.
When I open my connection to the database, though, it reports the typically useless message that the connection is not allowed and that the server may not allow remote connections.
How to I get past this? I've done everything right.
View 1 Replies
View Related
Jun 18, 2007
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)"
View 1 Replies
View Related
Jul 20, 2005
Is there anybody out there with a MS SQL 2K Security Baseline orSecurity Checklist. Where can I get one????Thanks in advanceDavid
View 1 Replies
View Related
Feb 28, 2008
Hi;
I am looking for a way to log all security related events for SQL in Windows Security Log. I am trying to use SCOM for monitoring SQL and I am looking at ways to generate alerts in my SCOM Console for specific events in SQL e.g. A table is deleted, user is modified, deleted, etc. Is this possible and if yes how do I achieve the same?
Rgds;
View 6 Replies
View Related
Aug 3, 2006
In an environment where there are many initaitors speaking to a central target with frowarders in between, from what i can understand this best policy is to disable encryption on the endpoints, since dialog encryption will be enforced this is all that is really required, is this correct.
If the endpoints used encryption the message would need to be encrypted and decrypted at each forwarder resulting in slower perfromance, where as dialog encryption would only encrypt at the sender and decrypt at the target, so is this the best way to go?
Secondly is it best practice to open a dialog initally and send messages over this dialog for years never ending the conversation? This way the services only have to authenticate eachother once, if there are no reboots etc that is of course.
I would think performance wise sending each message and ending the conversation each time is a much greater overhead ? So would it be best practice to keep dialogs open and keep sending messages ?
Initally when i was learning service broker i thought that one must send a message and end the dialog until the next message, but i think the other way is the best option ?
Is this correct ?
Thanx
View 1 Replies
View Related
Feb 19, 2007
Hi
I'm designing a distributed application where I will have SQL Server 2005 distributed databases replicating data to my central hub which is again a SQL Server 2005 database using SQL Service Broker. Data will be sent from the central hub to the distributed sites and vice versa. I need to authenticate the communication and also secure the communication by encrypting the messages. Which security shall I use? Where do I configure the type of security being used? What is the difference between transport security Vs dialogue security - Full security model?
Thanks
View 4 Replies
View Related
Nov 23, 2004
Hi,
Is there a format function in MDX which will help me solve the following
scenario:
One of my measures is Net Sales and I have tried all different formats
in Analysis Services so that it shows without the decimal places i.e.
instead of showing the Net Sales for any selected combination of
Dimensions as 123,456.04, I want to show it as 123,456
When I browse the cube (in Analysis Services), I can meet my
requirement. However, in my front-end (Microsoft Data Analyzer), I am
still getting it as 123,456.04 (even when the same in Analysis services,
i.e. when I browse the cube, is being shown as 123,456).
I do not have much choice at the moment and am stuck with Microsoft Data
Analyzer and unfortunately have not been able to solve this :(
Can someone think of a solution/workaround/use of MDX which will help me
get the results being displayed without the decimal. Is there a format function that I can use in MDX and how to use it???
Many TIA
View 2 Replies
View Related
Aug 5, 2006
Hello,What is uniqueidentifier as a data type?Also what is the data type for setting unique STRINGS ((nchar,nvarchar), for example to be used for emails and user names in a userregistration system).SQL Server does not allow me set primary keys for columns where datatypes are not INT.Thanks in advance.
View 3 Replies
View Related
Nov 20, 2006
I've been working on a performance review web application (i.e., employee's annual reviews done via the web). In the process of creating the application I've been teaching myself .NET - maybe not the best way to do it but I've been learning a lot. However, I still feel like I'm not doing something right.On each Page_Load I'm doing database work with a data reader: Reading the data in, displaying it, letting the user add, edit, or delete it, etc. So every Page_Load code behind looks like this: string sql = "SELECT UserID, Passwd, RecID, Name FROM UserList";
SqlConnection myConn = new SqlConnection("Server=BART; Database=WSSD; User ID=sa; Password=wss1231");
SqlCommand cmd = new SqlCommand(sql, myConn);
SqlDataReader dr;
myConn.Open();
dr = cmd.ExecuteReader(); And so forth and so on. Now since I re-use this code again and again - I imagine it's a good idea to implement my connection code in a class that I can re-use easily. But I have no idea where to start on something like that. What can I say? I'm a newb. A push in the right direction would be great.
View 8 Replies
View Related
Jan 5, 2008
I have the following tablestblUserdatausercode username firstname lastname5 peter peter smith11 john433 john doe15 simonsays Simon SmithtblEventsID postedbycode title eventtext createdate1 5 woodstock 'oldies' 12/12/20082 11 love parade 'dance all night 1/1/20083 11 spring break 'great party' 2/2/2006tblEventVisitorsusercode eventid5 15 311 111 211 3As you can see User John433 is going to 3 events.But I only want to select the one that has the first upcoming startdate bigger than now: getdate()Desired output would be:username firstname lastname eventid title eventtext eventdatepeter Peter Smith 1 woodstock 'oldies' 12/12/2008john433 john doe 2 love parade 'dance all night 1/1/2008simonsays Simon Smith NULL NULL NULL NULLHow can I make such a selection? (perhaps see this thread for similar info: http://forums.asp.net/t/1201266.aspx)Thanks!
View 16 Replies
View Related
Feb 12, 2008
I want to make simple database application but I want that I make just one transaction with the database..
If I have , say 10 insert queries, i want to transact with the database just once.
Somebody told me this could be done by 'containers' or 'data transfer objects'
So please, somebody help..
View 1 Replies
View Related
Mar 10, 2005
Hi,
I made a DTS which appends data coming infrom a view to an exisiting table.So far no problem and all goes well.
I am facing a problem due to the format of the date that is coming in (getting appended) and while going through BOL, came across the following topic:
mk:@MSITStore:C:Program%20FilesMicrosoft%20SQL%2 0Server80ToolsBookshowtosql.chm::/ht_dts_trns_97ou.htm
I tired the above tips but it appears that if I try to do this in my DTS (which appends data),the logic of the DTS will change. A single arrow also gets added whichI think represents a simple mapping/transformation rather than a append. To clarify my point, please note the attached image which represents that the data is being appended (due to the many sided arrows pointing to the source and destination - visible under the Transformations tab of my DTS).
Sincerely hoping that my post is clear, can someone help me find how to make changes in a DTS (which appends data) and ensuring that thelogic remains the same i.e. it should append data.
Many TIA
View 4 Replies
View Related
Jul 24, 2007
I have a function written in postgresql that I want to create in sql server (UDF). After effort of full day I am seding this request to please help me, here is the function:
CREATE OR REPLACE FUNCTION fn_comma_env(int4)
RETURNS text AS
$BODY$
DECLARE
rec record;
str text;
comstr text;
BEGIN
str := '';
comstr := '';
FOR rec IN SELECT class.classname FROM hostenv, class WHERE hostenv.classid = class.classid AND (hostenv.hostid = $1) LOOP
str := str || comstr || rec.classname;
comstr := ',';
END LOOP;
RETURN str;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE;
Thanks in advance,
Syed
View 1 Replies
View Related
Jun 20, 2008
select @xml = bulkcolumn from openrowset(bulk 'C:Documents and
SettingsKasiDesktopewsrss.xml' , single_blob) as channel
here after bulk instead of giving path, we have to give parameter so that the paramter takes the value from the table.
the tables contains paths of xml files
View 2 Replies
View Related
Jan 4, 2006
there is a prblem with data in pivoting the table.
problem is like this--
there is some data 'xy' and some data 'xy '. when i m giving 'xy' as a pivot key value it doesent recognise 'xy ' and viseversa..
i can't reduce the size of the datatype coz there is some data of diffrent size as 'abcd'.
this data is loaded from excel sheet to sql sever table.
wht can i do for this problem.
is there any method to truncate the indivisual data, i m using nvarchar datatype for this.
View 2 Replies
View Related
Jan 12, 2007
Hi friends,
I am backing up the database using the Database Maintenance Plan.
Everything is in place. I want to save the file name in the format yyyy-mm-dd_finbck.bak. how can i assign the date from here itself ? this should be done through the DB maintenance plan only.
is it possible ? can we assign the date to the backup file through the DB maintenence plan ?
kindly guide.
Regards,
Amit
View 4 Replies
View Related
Dec 10, 2007
Dear All,
Could anyone guide me to prepare for the Interview in SQL SERVER 2005.
I have studied the relevant things from background knowledge from the SQL Server books. But is there any specific areas in which I have to concentrate for the sake of Interview?
Thanks in Advance.
View 16 Replies
View Related
Jul 23, 2005
Is it possible to retrieve all tables that a given one is related tovia foreign keys?
View 2 Replies
View Related
Oct 26, 2006
Any idea the cause of this error. I got it while trying to execute a stored proc (in sql 2005). I had just restored database and below error is stucking me to move ahead. Any help please
Error invoking method 'ExecuteQuery' for transaction (Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding)
View 1 Replies
View Related
Jul 25, 2007
I have a function written in postgresql that I want to create in sql server (UDF). After effort of full day I am seding this request to please help me, here is the function:
CREATE OR REPLACE FUNCTION fn_comma_env(int4)
RETURNS text AS
$BODY$
DECLARE
rec record;
str text;
comstr text;
BEGIN
str := '';
comstr := '';
FOR rec IN SELECT class.classname FROM hostenv, class WHERE hostenv.classid = class.classid AND (hostenv.hostid = $1) LOOP
str := str || comstr || rec.classname;
comstr := ',';
END LOOP;
RETURN str;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE;
Thanks in advance,
Syed
View 1 Replies
View Related
Mar 20, 2008
Hi,
I have a data including "year", "month" and "day" columns and I'm using these columns as parameter to create a report. I'm using the parameters successfully in my report. I import all possible year-month-day combinations but in my data there are some unmatched combinations. (For ex, for 2007 there's just last 3 months of the year, and some days are missing) To make the report more affective, I wanna make the parameters dynamic; when I choose 2007 in year-combobox I wanna see just the related months (not all 12) and the related days after I choose the month. Is it possible in reporting services?
View 9 Replies
View Related
Mar 24, 2004
Hi all,
I am trying to insert user's input from a web form into the tables. For example,
PURCHASE TABLE(PurchaseID, PurchaseNo, Date, PartID)
PART TABLE(PartID, PartDescription,MachineID)
MACHINE TABLE(MachineID, MachineName)
On the web form I have textboxes for the Purchase No., date and part description, and a drop down list for the machine name. How do I insert them into the different tables?
I've just start learning ASP.NET and I am using Web Matrix for this. The examples I've seen so far only shows how to insert into a single table.
Thanks!
View 2 Replies
View Related
Nov 26, 2004
Locally I have a table named 'countrydetails' in sql server which is created by dbo. I create asp.net page trying to show the details of the country. I write a query in vb.net page and build the project and try to run it, its giving me results.
The same vb.net project is uploaded and put online and when I try to see the details of the country online inspite of having records i m getting no results. In this case the table which was created is having username other than dbo.
I want my query written in vb.net page to run irrespective of the different users who have the database table. The page should run for both dbo and other users also. How do I achieve this? Please advise.
Thanks.
Regards,
Sumis
View 1 Replies
View Related
