Looking For Input On SQL 2005 And IIS Security
Mar 29, 2007
I am just looking for feedback and maybe pointers for research regarding securing SQL 2005 for IIS access. I am currently working on a project for building a new retail website and our sysadm guys have some concerns regarding exposing our SQL box to the DMZ via direct connections to the IIS box. Now we have not completely come to a conclusion of saying that it is not possible, but we are very concerned due to recent credit card and customer data problems in the industry.
So far we have mainly talked about just exposing web services(limiting the exposure of data that can be retrieved) on an internal IIS box that would be called allowed to be accessed from only the IIS box in the DMZ using User Accounts and Client SSL Certs. What I am most concerned about is the performance of this design. I would like to try and provide a connection to the SQL box directly for the devs, but I am not sure what the best practice would be for securing this connection through the firewall and also monitoring it in case our DMZ IIS box gets owned.
Any input or direction to resources would be much appreciated. I have read quite a few papers so far and just want to get feedback on architectures and designs.
Thanks in advance.
View 1 Replies
ADVERTISEMENT
Nov 8, 2007
Hi all,
I need to have a check procedure which rejects long strings if they contain anything other than 'allowed' characters (alphanumeric characters and selected other characters - space,comma, apostrophe).
So in (very rough) pseudocode:
FOR EACH character in string
IF( chararacter is not alphanumeric AND character is not valid )
reject string;
(end checking)
Accept string.
What is the best was to do this in T-SQL??
Many thanks for helping a noobie,
Joe
View 2 Replies
View Related
Sep 27, 2007
I am designing a report which need to take parameter input by user when the report is open. Can anyone please tell me how to do this? Using MS SQL 2005 report. Thanks.
View 2 Replies
View Related
Jan 5, 2006
Hello,
I am trying to write my first couple Integration Services packages using SQL 2005. My configuration is a workstation running windows xp professional, and a windows 2003 server that is running the SQL server.
Anytime I run a package that accesses the remote server from my workstation, the job fails with an error code. The workstation cannot seem to run a package to load data to the remote sql server. Why is this? Is there a service pack, or hotfix coming out soon to correct this problem?
Additionally, I also seem to be unable to update a database using excel as the data source from which information should be used. If I import my excel spreadsheet into an access table, I can update the sql database from Access using integration services. Why can't I use an excel spreadsheet as the source? Is there a a service pack or hotfix coming out soon for 2005 sql that will correct this problem?
Thanks!
Jim
View 13 Replies
View Related
Feb 9, 2006
I have traditionally done web app and client server programming and am have been playing around with a new tool (Win forms) app that I eventually want to distribute to other developers and a couple of business people in our organization. I have done apps in the past that all connect to a central server for data access. The I'm working on now will have an individual DB per user and should be available locally for a desktop version of the software.
I am looking for more resources on things to consider when deploying an app with either MSDE or SQL 2005 Express. More specifically, items like long term maintenance of the db once it's installed with the user, etc. (DB bloat, transaction files, auto maintenance routines I may want to build in, etc)
I have seen all of the msdn docs on what you need to deploy (and how to do it), but I'm looking fro input from people that have deployed it and any significant pitfalls they have run into with that sort of deployment.
Any links or book references would be appreciated, thank you,
Cy Huckaba
View 1 Replies
View Related
Dec 12, 2007
While recently working with several mining models, I came across something that struck me as pretty odd - and I'm hoping to find an explanation for the behavior.
Consider the following setup:
A single table in the relational database represents the only case table
A single, continuous column is the predictable
A mining structure has been created
The mining structure contains a single model, based on the MS Decision Trees algorithm
Input columns were selected for the model via the BI Studio wizard (i.e., those provided via the "Suggest" button)
The structure has been fully processed
Now, the interesting parts:
I view the scatterplot for the mining model, under the Mining Accuracy Chart tab
Back on the Mining Structure tab, I delete one of the input columns
I add the same column back into the structure
The structure is fully processed again
When I view the scatterplot for the mining model, under the Mining Accuracy Chart tab, a different set of data points are presented for the model predictions
A different set of decision trees under the Mining Model Viewer tab confirms thisHow could different patterns have been found this second time around, even though all of the input columns were the same (as well as the training cases)?
(Note: I encountered this situation while creating a new mining model that was identical to an existing one. Even though the models received the exact same inputs and training cases, they yielded different results. I was able to reproduce the behavior by using steps 1-6 above, though.)
Can someone provide some insight on this behavior, or some kind of explanation of what may be happening?
Thanks,
Joe Miller
View 3 Replies
View Related
Feb 23, 2008
Hi all,
In a Database "AP" of my SQL Server Management Studio Express (SSMSE), I have a stored procedure "spInvTotal3":
CREATE PROC [dbo].[spInvTotal3]
@InvTotal money OUTPUT,
@DateVar smalldatetime = NULL,
@VendorVar varchar(40) = '%'
This stored procedure "spInvTotal3" worked nicely and I got the Results: My Invoice Total = $2,211.01 in
my SSMSE by using either of 2 sets of the following EXEC code:
(1)
USE AP
GO
--Code that passes the parameters by position
DECLARE @MyInvTotal money
EXEC spInvTotal3 @MyInvTotal OUTPUT, '2006-06-01', 'P%'
PRINT 'My Invoice Total = $' + CONVERT(varchar,@MyInvTotal,1)
GO
(2)
USE AP
GO
DECLARE @InvTotal as money
EXEC spInvTotal3
@InvTotal = @InvTotal OUTPUT,
@DateVar = '2006-06-01',
@VendorVar = '%'
SELECT @InvTotal
GO
////////////////////////////////////////////////////////////////////////////////////////////
Now, I want to print out the result of @InvTotal OUTPUT in the Windows Application of my ADO.NET 2.0-VB 2005 Express programming. I have created a project "spInvTotal.vb" in my VB 2005 Express with the following code:
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.SqlTypes
Public Class Form1
Public Sub printMyInvTotal()
Dim connectionString As String = "Data Source=.SQLEXPRESS; Initial Catalog=AP; Integrated Security=SSPI;"
Dim conn As SqlConnection = New SqlConnection(connectionString)
Try
conn.Open()
Dim cmd As New SqlCommand
cmd.Connection = conn
cmd.CommandType = CommandType.StoredProcedure
cmd.CommandText = "[dbo].[spInvTotal3]"
Dim param As New SqlParameter("@InvTotal", SqlDbType.Money)
param.Direction = ParameterDirection.Output
cmd.Parameters.Add(param)
cmd.ExecuteNonQuery()
'Print out the InvTotal in TextBox1
TextBox1.Text = param.Value
Catch ex As Exception
MessageBox.Show(ex.Message)
Throw
Finally
conn.Close()
End Try
End Sub
End Class
/////////////////////////////////////////////////////////////////////
I executed the above code and I got no errors, no warnings and no output in the TextBox1 for the result of "InvTotal"!!??
I have 4 questions to ask for solving the problems in this project:
#1 Question: I do not know how to do the "DataBinding" for "Name" in the "Text.Box1".
How can I do it?
#2 Question: Did I set the CommandType property of the command object to
CommandType.StoredProcedure correctly?
#3 Question: How can I define the 1 output parameter (@InvTotal) and
2 input parameters (@DateVar and @VendorVar), add them to
the Parameters Collection of the command object, and set their values
before I execute the command?
#4 Question: If I miss anything in print out the result for this project, what do I miss?
Please help and advise.
Thanks in advance,
Scott Chang
View 7 Replies
View Related
Apr 14, 2006
Hi all !
I have a question regarding the security of SQL Server 2005 Express Edition.
What securities options that SQL server 2005 EE provide for its users?
I know that after I deploy my website to a hosting company or a web
server, my database file will be on the net. That means everybody can
type in the file name and download my database file then open it. Just
like that. For example, my database filename is EXAMPLE.MDF. then,
someone just goto my website, say
http://www.cool.com/app_data/example.mdf, and download the database.
To prevent that to happen, what the securities options that available to me beside putting password on my database file?
Thanks for taking time to answer my question. Any help will be appreciated. Have a good day!
View 7 Replies
View Related
Oct 11, 2006
Hi all,
Could anyone suggest me which is the recommended authentication mode for web applications with MS SQL Server 2005.
Also let me know how the new security features of MS SQL Server 2005 can be used for secured application access.
Thanks in advance
HHA
View 1 Replies
View Related
Mar 28, 2007
(1) To prevent unauthorised database access, is it ADEQUATE to delete / disable the BuiltInAdministrator login and the guest (database) user ?
(2) How can I delete / disable the BuiltInAdministrator login
in SQL Server 2005 Express ? It didn't allow me to disable or delete it.
(3) How can I delete / disable the guest (database) user
in SQL Server 2005 Express ? It didn't allow me to disable or delete it.
View 3 Replies
View Related
Jul 4, 2007
HI,
I am just starting out with SQL Server 2005 and really getting in a muddle with al the security stuff.
It seems i have LOGINS, DATABASE USERS, Server Roles, Database Roles, SCHEMAS and somehow they all tie in together. I am using Microsoft Press "SQL Server 2005 Implementation and maintenance" but it really isn't doing a good job of explaining it to me.
Can anyone point me to a reference that clearly explains all this stuff?
View 2 Replies
View Related
Apr 24, 2008
Hello, everyone
we have a tech department that adding new databases (restore from a backup), creating new logins and
assigning deferent database roles to those logins.
They used to have a sys admin role assigned on the system. They are using a windows account to connect.
I am planning to revoke that sa privilege from them and gave them dbcreator server account rights.
Apparently it's not enough. Any suggestions?
thank you
View 4 Replies
View Related
Mar 27, 2007
Hello,
I found scary topics with security treads for SQL 2005:
http://blogs.msdn.com/ikovalenko/archive/2007/01/15/db-securityadmin-is-very-powerfull-and-dangerous.aspx
http://blogs.msdn.com/ikovalenko/archive/2007/01/15/sql-server-2005-building-security-model-based-on-triggers.aspx
Igor has mentioned, that SQL Server team replied about db_securityadmin topic than this person should be trusted. I'm not buying this , especially for case if account/login for db_securityadmin will be compromised and hijacker will be aware about this options and can elevate own privileges and make a damage..
So I'm looking for clear answers there...
View 3 Replies
View Related
May 28, 2008
I am an Oracle DBA who inherited SQL Server administration. I have been to some 2005 training and I've been supporting several DB's for a while now but I still have some nagging security questions and would appreciate some help.
1) I needed to grant execute on a specific procedure but when I drilled down, I found that it already had execute in the EFFECTIVE PERMISSIONS. I would like to know how to tell where it got this permission from. I did some digging and found that execute appears to have been granted to the schema itself. I didn't know you could do that. Would this result in the effective permission that I observed?
2) I am trying to audit the permissions on existing principles. In Management Studio I drilled down and found permissions under Security and under Server Properties. There are also more permissions under Database Properties and Security and still more assigned at the specific object level. Where can I go or what can I query to see ALL the permissions a principle has been granted across the entire server?
3) If I grant a principle CONTROL to a schema does that also automatically confer DDL rights to said schema or would additional privs be required to perform DDL?
Thanks in advance,
Roger Westbrook
View 1 Replies
View Related
Jan 28, 2006
Hi All,
I have been building a database in SQL Server Express for some months now using the Windows level authentication login that has given me full access to everything in the database.
The time has now come where I need to create user accounts and grant permissions to specific stored procedures and I'm having trouble doing this.
Can anyone give me a brief rundown of the required T-SQL commands I need to set up a user account that can do nothing but run stored procs (not the system procs which apparently are being discontinued: http://msdn2.microsoft.com/en-us/library/ms182795.aspx).
So far I have:-
CREATE LOGIN db_test_user WITH PASSWORD = 'eXaMpL3Pwd
USE db_new
CREATE USER db_test_user
The above code executes successfully but when I try and connect (using Management Studio) I get error message 'The user is not associated with a trusted SQL Server connection (error 18452)' which means little to me.
I also tried creating a 'WITHOUT LOGIN' user for the database but could not figure out how to give it a password.
I don't know (/understand) roles/schemas and don't know if I really need them as I only need user access to specific stored procs. I don't ever want them to see the tables for example so they only need the most restricted access.
Can anyone help?
Thanks,
DG
View 2 Replies
View Related
Dec 7, 2007
Can a regular user configure DBMail on a SQL 2005 Server?
Thanks
View 1 Replies
View Related
Dec 14, 2006
I cannot block user access to tables. I have restriced accounts at theserver, database, group, schema, and table lavel and can still opentables right up.In other words, I have absolutely no security. Any ideas?
View 1 Replies
View Related
Mar 29, 2007
Don't know if this is possible or not:
Goal: set security on running specific stored procedures based upon user login and databse access
I have some DBA's who want to retain full control of databses / stored procedures as they now have but I want to restrict or rollback some of the changes that were implemented when the sql 2005 was set up. The sql 2005 EE is in a clustered system and uses Mixed Mode Authentication.
An example of what I want to restrict: The DBA's want to be able to view and kill processes for the different databases that are installed under their instance. The problem is other customer databases are also under the same instance.
Is their a way I can combine or have the stored procedure sp_lock only show the processes for the databases they have access to based upon their login? My concern is they will kill a process and affect the other customers.
Thanks for any info or pointers.
carl
View 1 Replies
View Related
Jun 13, 2007
Does anybody have a link to either of these two documents. My company is getting ready to go through an audit and we need some firepower and to know what is expected. Any help with obtaining microsoft SQL Server 2005 best practices documents is appreciated.
-Kyle
View 3 Replies
View Related
Feb 2, 2008
Couple of questions for the SQL Server Guru's out there.
SQLServer 2005
Web Hosting Provider
Ok I am developing a Web application in ASP.NET with AJAX, etc. etc. It will be some time before it is ready to roll out. As a mockup I created the same application in Microsoft Access and Visual Basic (VS 2008), which I can link the tables to the hosting provider on the internet. Works very well and speed is very acceptable. The want to start utilizing it with the mockup distributed app that I created.
My question is, is how secure is the data that is moving from the local application to SQLServer 2005 with the web hosting provider ? Is there anything that I can do to increase security ?
Thanks, any thoughts ?
Appreciate any suggestions or comments.
View 1 Replies
View Related
Feb 27, 2007
Has anyone compiled a list of out of the box features to disable (like sql mail in 2000)? Or even just a general security checklist for 2005?
I'm looking for one similar to http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sp3sec04.mspx
View 1 Replies
View Related
Sep 21, 2005
I've been reconfiguring my Windows service accounts for the SQL Server service and the SQL Agent service to comply with the security best practices for SQL Server 2005. Specifically, I created two new network accounts. One account runs the SQL Server service, the other runs the SQL Agent service.
View 1 Replies
View Related
Apr 12, 2007
Hi,
I'm having a lot of trouble trying to set up a custom security extension with Reporting Services 2005. Following the VB example from McGraw Hill Osborne (http://www.mhprofessional.com/product.php?cat=112&isbn=0072262397&cat=112), I've compiled the .dll for the extension and made the changes to the ReportManager and ReportServer .config files. After I reset IIS and return to the RS website, it displays "The report server has encountered a configuration error..." and I get a message in my System Event log:
The application-specific permission settings do not grant Local Activation permission for the COM Server application with CLSID
{BA126AD1-2166-11D1-B1D0-00805FC1270E}
to the user MYMACHINEASPNET SID (S-1-5-21-1708537768-839522115-1343024091-1005). This security permission can be modified using the Component Services administrative tool.
Wondering if anyone else has had similar trouble and how did they get around it?
View 2 Replies
View Related
Jul 3, 2007
Hi,
I wonder if SQL Server 2005 supports row based security?
I need to set some users to see data filtered by a specific field and value...
Example: User XPTO only sees data about vendor code = '123'
Is this possible in the box?
Best Regards,
View 1 Replies
View Related
Nov 5, 2006
Dear mems,
I have a problem, and i don't khow how to resolve, pls help me:
My server is Windows XP (not domain),
I work with SQL Server 2005, installed on my server,
I configurate my SQL SERVER connection is "Windows Authentication mode",
I add user "MyComputerguest" to MyServerSecutityLogins to accept connections from local network
I have many databases: Db1, Db2, Db3...
But, I don't know to configurate my SQL Server to achive these:
1) My clients using "SQL Server Managment Studio" connect to Databases Db2, Db3... on my Server, they can expand, modify, add new all Tables, Sp, functions of Db2, Db3.
2) My clients are not allow to access Db1.
2) My clients can add new Databases Db4, Db5, Db6... in the future and they have full permission on every Database which they create without my interfere.
Best regard,
View 1 Replies
View Related
May 15, 2008
Hello,
I have followed all the steps indicated in the Security Extensions Sample for RS 2005, and I have not changed any webpage code. However, I get the following errors:
- when accessing http://<computername>/ReportServer Access to the path 'C:Program FilesMicrosoft SQL ServerMSSQL.3Reporting ServicesReportServerlogon.aspx' is denied
- when accessing http://<computername>/Reports Error message 401.3: You do not have permission to view this directory or page using the credentials you supplied
Can someone help please?
Thank you!
View 1 Replies
View Related
Aug 20, 2006
if you run the following script it takes access from the windows admin from getting into sql server through windows auth. The issue is that the files that are attached logging as SA after that are read only. Is there any solution? When you try and switch the file to read write sql server gives an error saying that it cant read the mdf and ldf--gives a windows access error....
USE [master]
GO
IF EXISTS (SELECT * FROM sys.server_principals WHERE name = N'BUILTINUsers')
EXEC sp_dropsrvrolemember [BUILTINUsers], sysadmin
DENY CONNECT SQL TO [BUILTINUsers] CASCADE
GO
IF EXISTS (SELECT * FROM sys.server_principals WHERE name = N'BUILTINAdministrators')
EXEC sp_dropsrvrolemember [BUILTINAdministrators], sysadmin
DENY CONNECT SQL TO [BUILTINAdministrators] CASCADE
GO
IF EXISTS (SELECT * FROM sys.server_principals WHERE name = N'NT AUTHORITYSYSTEM')
EXEC sp_dropsrvrolemember [NT AUTHORITYSYSTEM], sysadmin
DENY CONNECT SQL TO [NT AUTHORITYSYSTEM] CASCADE
GO
View 4 Replies
View Related
Apr 10, 2008
I am not sure if this is the right place to post this but I need some help. We have an email server, with windows 2003 server, set up with SQL server 2005 and sql server management studio express which uses windows authentication. I noticed in the event viewer we keep getting an error under the application, "failure audit". about 25 a minute. The error says "login failed for user admin
The user is not associated with a trusted mysql server connection." followed by an ip address
(this username changes on a daily bases which makes me wonder if it is an attempted hack)
I have little knowledge of sql. How can I get this to stop happening. Event Viewer returns no results to help me fix this and I am having no luck researching it on my own. If there is any more info I need to provide, let me know. HELP!
View 3 Replies
View Related
Dec 8, 2006
Hello,
I have a Problem .
My Users from the Reportingservices cannot change in the Abonnements Propertis the Field " TO" Email Address.
I have set the following Security Permissions.:
- View Reports
- Manage Individual subscriptions
- View Ressources
- View folders
- Consume Reports
When i set the "Manage ALL Subscriptions" ,the Users can Change the "TO" Email Address.
Can anyone give me a Tip does Users set the "TO" Field without the Permission "Manage ALL Subscriptions".
I won't give my Users the Permission to Manage ALL Subscriptions.
Many Thanks
View 4 Replies
View Related
Aug 7, 2006
Hi everyone,
I'm currently investigating the security improvements of SQL Server 2005. I've got some problems with the schemas introduced in SQL 2005 and security settings.
For my test I've created two schemas: UserManagement and Sales. A user "test" is attached to the UserManagement schema. There's a table Sales.Users containing a list of users (varchar) and a stored procedure named UserManagement.AddUser that can be executed by the UserManagement schema (GRANT EXECUTE, so "test" can execute the SP). UserManagement.AddUser simply inserts a new row into Sales.Users.
Because the Sales schema doesn't contain any user, nobody (except the sysadmin, of course) can do a INSERT/SELECT/DELETE in the Sales.Users table. As expected, the following SQL statement fails:
EXECUTE AS LOGIN='machine est';
INSERT INTO Sales.Users VALUES('Test User');
INSERT was not allowed: object 'Users', database 'test', schema 'Sales'.
The second way of inserting rows into Sales.Users is to execute the stored proc UserManagement.AddUser:
CREATE PROCEDURE [UserManagement].[AddUser]
WITH EXECUTE AS CALLER
AS
INSERT INTO Sales.Users VALUES('Test User');
The user "test" can execute this sproc without problems:
EXECUTE AS LOGIN='machine est';
EXECUTE UserManagement.AddUser;
(1 row(s) affected)To my astonishment the INSERT statement inside the stored proc does execute - although UserManagement.AddUser and Sales.Users are two different schemas. Why is that, is there a chaining happening? To my understanding SQL Server should test INSERT rights on Sales.Users for the UserManagement schema and deny the INSERT statement because UserManagement isn't allowed to INSERT in the Sales schema.
Any ideas? Help regarding the issue is greatly appreciated.
Best regards,
Alex
View 3 Replies
View Related
Dec 3, 2006
hi
i am using vc# 2005 and sql 2005 express.
my problem is does exist any way to set password on sql2005 database ?
(because i don't want to end users access to sql server's tables and datas and ...)
how to solve my problem ?
thanks
View 3 Replies
View Related
Nov 20, 2006
Hi,I have a ASP.net 2.0 web app which i want to run on IIS. It has a database file stored in APP_DATA folder. I have set "UserInstance" property to False, as want to access the same database file from another app, which can do its own modification. So basically i want the same database to be shared. Now after putting the web app on IIS i am getting error like :Login failed for user ''. The user is not associated with a trusted SQL Server connection.Any idea of how to solve this?
View 1 Replies
View Related
Mar 29, 2007
We have 2005 Reporting Services fully functional - Dev, QA and prod (farm). I have at least 5 distinct business group trees in my structure, who have inherited or modified security based upon NT Users and AD groups. Now, we want to replace a major group (<domain_name>Domain Users) with a controlled group.
I cannot find anyway to report or map the folders that a specific NT User and AD group has rights. Cannot it be done?
View 1 Replies
View Related
