I have a situation where I have an app that uses a sql server (msde)
database. The app will be used in environments where no one should be
able to manipulate the data except the developers (app admins) - not
even site database admins. When the application and msde is installed,
a default instance of the database gets attached to msde or built by
script. by default, a built in server acct and approle acct exist to
secure the data accordingly with passwords concealed. What can be done
to keep someone from copying the mdf and ldf files to another machine
where they have admin rights and manipulating data?
Hey everyone - I get the following error when I try to open up a DTS package that I ported from one machine to another as a file. The error is:
Error Source: Microsoft Data Transformation Services (DTS) Package
Error Description: The parameter is incorrect.
Does anyone know what this error is and how to get the package to open? I really don't want to have to rewrite the package if I can avoid. Any help would be greatly appreciated.
I have started a new site using a CMS which can be either Access or MS SQL. For ease of install and cost factors the site is currently running as an Access dbase driven site. If traffic numbers and content grow and Access dbase blows out to, lets say 500meg will I see a major degradation in performance and will it be possible at a later data to somehow export the Access dbase data into MS SQL dbase format. I'm assuming I'd have to engage a fairly competent developer but before I get into the trap of growing a large site, I'd like confirmation that I'd be able to extract all of the data and get it into a more robust solution as and when required. phew!
Has any body done the porting from oracle to sql server, what were theissues in porting the data bases?Also suggest some resources which can be helpful in the porting projectTIAGolu
1) In several tables, in my MySQL version, I created columns usingsomething like the following:`ab_timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on updateCURRENT_TIMESTAMP,This allowed me to ensure that when a record is either added or edited,the value in the field is set to the current date and time. I.E.,ab_timestamp is given the current date and time when a record iscreated, and then it is updated to the date and time at which therecord is updated. I learned the hard way that MS SQL does not like"on update CURRENT_TIMESTAMP". So, it looks like MS SQL will allow meto initialize ab_timestamp to the current date and time, but notautomatically update it to the date and time at which the record isupdated. I have plenty of code to port that depends on the behavioursupported by MySQL. DO I have to modify all that code, or is there away to get MS SQL to provide it? (Yes, I know 'timestamp' isdeprecated in MS SQL and that I should use datetime instead, and infact have already done so.)2) I began with a single SQL script that creates all the tables, views,functions and triggers the database needs. On trying to get MS SQL toaccept it, I encountered a number of error messages saying that CREATEFUNCTION and CREATE VIEW need to be the first statement in a script.Why? I know I can work around this odd constraint by putting eachfunction and view (and IIRC trigger) into its own script, but thatseems like a make work effort imposed for some unknown reason by MSSQL, unless there is another way to get around it.3) I see, in the documentation for CREATE FUNCTION, functions are notallowed to use a timestamp for either a parameter or a return value.This is in reference to a pair of scalar functions I am using whichneed to manipulate date and time values. For the purpose ofclarification, is this documentation refering to all date/time datatypes, or only the deprecated timestamp type? As examples, considerone function that needs to return the most recent date in a date columnin a specific table, or another function that computes a date from adate and an offset (e.g. if called with the value returned by the firstfunction as the first argument and '-7' as the second, returns the dateof the day that is a week earlier than that date). These two functionsare frequently used in the SQL code I'm trying to port and I reallydon't want to complicate so many of those statements if I don't haveto.ThanksTed
I am still pursuing my studies. I have been recently assigned a project for building Database Application for my college's library. And there was no better option to implement it with VB.NET 2.0 & SQL Server 2005. VB.NET 2005 & SQL Server Express 2005 are the resources at my disposal.
The problem is that the college already has a Library Management System in place, built in VB6.0 with Access. I have been asked to pepare the application from scratch. But the database obviously cannot be left out.
Now, can anyone suggest me an efficient way to port the Access database to SQL Server express. Also, the databse has been desinged poorly & is not properly normalized. Can I port it to SQL Server Express with some slight modifications to its structure without any loss of data??????
We rewrote one of our legacy C#/asp.net applications that accesses a sql server 2000 database. The new database schema looks very similar to the old one. The major difference is that some of the atributes in the database tables are different. But pretty much we are using the same tables, plus or minus a few. We need to import the old data into the new database. I have never had to undertake this type of thing before as I am not a DBA, but a developer. I feel a little scared about this whole process. My boss is open to us hiring a contractor to help with the process. My biggest concern is the referential integrity of the database. Can someone help me out. Does this sound like something that can be easily done or should I ask for some help. Ralph
hi, i got the application(from my first post) working in win2003. but this application was made in webmatrix, i.e it had only 2 files, the .aspx file and the web.config file. so i decided to convert this code to a vs.net web application project. i did that and got it working on my xp box. i copied the app folder on 2003, made that folder as an app in iis 6 of win 2003 and tried to run it, and got the following error:-
Server Error in '/emrtd' Application.
Cannot open database requested in login 'ASPState'. Login fails. Login failed for user 'HPSISandeshD'. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.Data.SqlClient.SqlException: Cannot open database requested in login 'ASPState'. Login fails. Login failed for user 'HPSISandeshD'.Source Error:
An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below. Stack Trace:
[SqlException: Cannot open database requested in login 'ASPState'. Login fails. Login failed for user 'HPSISandeshD'.] System.Data.SqlClient.ConnectionPool.GetConnection(Boolean& isInTransaction) +472 System.Data.SqlClient.SqlConnectionPoolManager.GetPooledConnection(SqlConnectionString options, Boolean& isInTransaction) +372 System.Data.SqlClient.SqlConnection.Open() +384 System.Web.SessionState.SqlStateConnection..ctor(String sqlconnectionstring) +92
Can anyone help me in this issue: How do we replicate database changes (like tables) from QA to production without losing Production data. We already tried using the DTS export but it is dropping the destination tables before export which will result in data loss in the destination database.
I'm using connection managers for all the connections i have in my packages in one project. However, if i change from one environment to another, i have to go to each connection manager in each package just to set the connection.
is there a faster way to change them like a configuration file lookup or something?
I have problems while using the Linked Server in MS SQL Server 2000 for data porting.
The Scenario :
I have about 900 hundred tables created in SQL Server database. These tables are freshly created and has no records.
I have created a Linked Server with a DSN connecting to a Sybase database from which the data has to be ported to the newly created tables in SQL Server.
The database creation as well as data porting is done by a Delphi application by executing the scripts in several .sql files.
I have shown an example script below which does the data porting.
INSERT INTO TEST_DATA (COL1,COL2,COL3) SELECT COL1,COL2,COL3 FROM [LINK_SYBASEDB]..[DBA].[TEST_DATA]
The Issue :
I often get the below error which stops the data porting process ( the error is logged in the Errorlog by the Delphi application )
My database knowledge are with MySql and Oracle, but recently I was asked to evaluate the migration of an existing (and maybe more) from ms access to sql server. My question is simple, if all of the sql are hard coded into the code ... how well this sql will work, I mean is the sql between access and sql server are plug'n'play ? However in any case, I always rewiew all of the sql.
Understand, I have developed a number of applications using RDBMS,including MySQL, PostgreSQL and MS Access, but this is my firstexperience with MS SQL. I'd bet my bottom dollar that MS SQL supportswhat I need, but I just haven't found where it is explained in anydetail in the documentation I have. The pages I have found strike meas a little too terse for my needs.In MySQL, I used statements like:PRIMARY KEY (`ic_contact_id`),KEY `ic_planner_id_k_tmp` (`ic_rep_code`)at the end of the SQL statement that creates a table. The primary keyhad to be unique but the other did not. Defining the non-unique keypaid huge dividends in the performance of certain queries, sometimesleading to orders of magnitude improvement compared to when the KEY wasnot defined (a few seconds vs tens of minutes). In joins, these keysrelate to primary keys in other tables that function as lookup tables.Otherwise, their primary role is for aggregation functions (max, min,&c.) in relation to group by clauses. The performance improvementsfrom having the KEYs defined are greatest in the latter.I have learned the hard way that MS SQL seems to like my primary keyclauses but not my KEY clauses. I don't know, and at present don'tcare, if this is because MySQL supports my KEYs as an extension to thestandard, or if it is a matter of the two RDBMS interpreting thestandard differently, or something else. What I need to know right nowis how do I obtain in MS SQL the same benefit as the MySQL KEY providedto me.A second question is that, in studying the documentation for the createtable statement, I saw reference to clustered vs non-clustered keys (atleast I assume they relate to keys since they immediately follow, andare indented from, the primary key and unique keywords). What exactlyis clustered and why? BTW, my primary understanding of "clustering"derives from work with numerical taxonomy and biogeography, but I'dwager that is something completely different from any clustering donein an RDBMS.I'll appreciate any clarification you can provide.Thanks,Ted
I'm working on porting a solution running under WM 5 Pocket PC to Win32 but I got some problems with type definitions like CEOID, CEGUID... I saw that these types are defined in windbase.h (EDB definition added to preprocessors) ) which includes types and definitions usefull for database managment. Anyway this header file is not present in C:Program FilesMicrosoft Platform SDK for Windows Server 2003 R2Include, so I was wondering which is the header file I can use instead of it. Thanks
Hi, I am using ASP.Net 2005 with C# language and SQL SERVER 2005... I am developing an web based application and have to deploy it on server. I need to prevent my site from the SQL Injection and have to use some algorithms. What is the best technique or method (Algorithm) in .Net ? Give some measures to prevent from Hackers.
We are looking for a way to tightly secure the database of a product being developed in MSDE 2k & C# so that even the db design cannot be viewed or data retrieved through any migration tools.
The NetLib database security tool perfectly matches our requirement but is overpriced. Any suggestions on the next best alternative?
Hi All, I am currently creating a SQLServer 7 server. This server will be used to host customer databases that I will restore on to the server. However, I want to prevent these customers accessing any other databases on the server, apart from their own. By removing the public database role from each customer database, and granting them very limited rights (basically exec rights on their own Stored Procs)on their own db, I plan to limit them to their own db. However, my problem is this: As you cannot remove the public role from the master db, a user could easily exec the following in a stored proc to read from the master:
Select * from master..sysusers
How do I prevent the users from accessing the master in this fashion. Will removing every permission from the public role in master be enough? Will removing every permission from the public role in master have any other side effects? Will removing the public role from other user dbs be enough to secure them?
Any suggestions/pointers would be appreciated. Gary.
Morning Guys, I'm trying to figure out a way of securing a DTS package and understanding how it works more and more. I have system administrators that have accesss to sql server. As dbas here we work with dts packages. We would like our packages secured from the system administrators that want to poke around with our work. how would we lock our objects down without messing them up from executing. The packages have been created under the servernameAdministrator. servernameAdministrator is the owner of the package. What would be the best way to start to understand all this.
1). Using an owner password a user password 2). Denying access to the sp_add_dtspackage & sp_get_dtspackages... 3). When generating a DTS RUN util to make a job using the dts package usually the password is embedded in the string even after encrypting the pacakage in clear text.... any suggestions to lead me in the right direction...... jonathan
If you have an owner password with no user password, you cannot execute the package without the owner password. Click OK to continue saving.
Rayd Abdou writes "hi all, i have an SQL server at my home and i think i got hacked from it :( and i really want to know what to do to secure the SQL Server from, disable permissions ? what commands ?
One of my client wats to protect his database so that if some body takes the backup he/she is not able to view data either directly or from the application i am delivering (may be he can buy my software and use his database or simply use demo version of my data)
Previously I used Access database and use database password protection (which every body knows is not good enough).
Now what I should do to protect my database (I am not worried about database structure or other objects but clients data that he will enter into the software like accounts data)
I need a moderate and a hard solution so that depending upon clients ability to affort I can implement at client side. There is no need to deliver protection in distrbution of my software.
can we secure mdf file, if it's copied from one location to anothercould not be used ???*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
I have been given a task of securing an SQL server 2005 that is currently open to SQL injection attacks. I have identified 3 main areas that I need to secure, these being:
1.Different SQL server logins - currently all database work from the site is performed using the sa account (don't ask me why they've left it so open to attack, I've not long started here!)
2.Custom error pages - to reduce feedback to a potential attacker on the database structure
3.Query Validation - any dynamically generated queries will be passed through a validator in order to possibly strip out any commands that we identify as those that an attacker would attempt to pass via the url.
Obviously, point number one is the big one. Based on this, my question is, what are the series of steps I would need to go through in order to;
a) setup a user login that has read access to many of the database tables (and execute access to some of them)
b) setup a user login that has read/write/update/execute access to other tables and stored procedures
I have read a lot about schema's, but I haven't had that many dealings with SQL server 2005 (yet), and haven't been able to find a step-by-step guide to setting up a schema/users and assigning permissions to them.
If someone could point me in the right direction of an "idiots guide to", that would be great, or if theres anyone that could list the steps I need to perform, that would be even better.
Also, if anyone has any other suggestions about how i could secure the server, I am all ears.
What is the best way to keep the data secure in my SQL Server 2005? and what is the best way to secure the communication between the client application and SQL Server 2005?
I have what some might consider a dumb question but I really don't know the answer. Until recently all our .Net work ahs been hosted on our internal network and the Sql Server (2000) was not open to the outside. However recently our company is looking at hosting other outside SQL Server applications that require users across the country to connect directly to our SQL Server (not through an ASP.Net app). The concerns we have is that ASP.Net runs on the NETWORK SERVICE account. If a user outside our network were to know the IP and name of onw of our databases could they connect with ASP.Net using a Trusted Connection or do trusted connections only work if the application is hosted on the same network? One of the applications we are looking at hosting is showing a list of all databases on our server (I did find the article on modifying sp_MSdbuseraccess but that didn't seem to work) so if someone got a hold of this list would they be able to connect? Thanks
I want to make some steps towards securing production database.
1. Give limited rights to Developers, i.e. db reaonly, db writedeny 2. Make strong password for local and Domain 3. Use Windows authentication 4. Enable log for 'Failed Login' attempts.
Hi, I€™m trying to secure my SQL Server 2005 infrastructure, and I€™m seeing that some sites are recommending that certain extended procedures be restricted to sysadmin only.
http://www.sqlsecurity.com/FAQs/SQLSecurityChecklist/tabid/57/Default.aspx This site recommended securing the following extended procedures:
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=3184075&SiteID=1 This thread recommended (implicitly) securing the following extended procedures:
Looking at these lists, I can see they might have missed other extended procedures like xp_regwrite, xp_regdeletekey, and xp_regdeletevalue.
My questions are: Is there any way I can find an exhaustive list as to what extended procedures should be restricted? Is there a website/Microsoft resource that can help me identify what to restrict?
Any other information you can point me to to secure our infrastructure would be appreciated.
Hye guys, I am not the perfect database designer nor the programmer. I have designed and developed a simple database application which uses VB as frontedt and SQL as backend. My Program worked fine.. Now I have 2 deploy it in clients computer where DBA is another person by which I am worried abt the data in the table. As X person is a DBA there he can easily change data of my tables in the database.
So I want an easy way by which the X person can't edit the data of the tables of my database only I can change the contents of my tables but i should be able 2 change the data from my program only..
I'm without a clue when it comes to SQL and how to secure it!
I've set up a SQLExpress running on a dedicated server on the web and I'm using TCP/IP remote connection to connect to the DB from a the web server running the ASP .
Would it be better using named pipes?
Also is there some way I can additionally authenticate a connection based on IP numbers?
