I have created logins and user accounts in SQL Server.
How come, when I open SQL Server, it did not prompt for passwords ?
How can I make it to do so ?
********************************************
*** Thank you very much in advance, security gurus ***
********************************************
SQL2K SP4
The apps that connect to my SQL DB's (for internet use) use SQL
authentication (the app login so to speak). Anyways, a Java developer showed
me that he built into his app a way to retrieve that app login and password
from SQL Server. Obviously I wasn't very happy about this. So my questions:
1; Does anyone know if the same thing can be done using .NET code?
2; I've heard of a method using .Net Web Services (WS) for using WINNT
authentication even for internet apps. It would be that the app calls a WS,
that WS the passes in WIINT authentication to the DB and all is good. Has
anyone else out there used this type of security? Is there a link you can
provide?
3; If number 2 is implemented, does it then eliminate the possibility of
apps being able to retieve the sensitive information?
TIA, ChrisR
I want to create an MS Access front-end for my SQL database.
I want to create user accounts with passwords for each user.
Should I change SQL Server to "MIXED authentication mode" (SQL Server authentication and Windows authentication) ?
********************************************
*** Thank you very much in advanced, security gurus ***
********************************************
Hello,
I realize that I am confused about SSIS and security.
In BIDS, I work on and modify my packages. That part I understand. Then, I want to build my project, then deploy to SQL Server 2005. I know how to do that too (for the most part, please see below):
My confusion arises around the "Protection Level" options in the package properties. Right now I have everything set to "EncryptSensitiveWithUserKey". My understanding is that I need to change this in order to run my packages from SQL Server jobs, because only the creator of the package can currently run the job.
So my question is, since I want to deploy to SQL Server, don't I want to change the "Protection Level" to "ServerStorage"?
However, it will not let me change the protection level to ServerStorage. It says "The protection level, ServerStorage, cannot be used when saving to this destination"... presumably because it is attempting to save to the file system.
However, even when I built out my packages and saved to SQL Server, I could not change the protection level either.
Which is why I am really confused... Once you save your packages to SQL Server, how do you make changes to the packages, so that the changes are reflected in the packages stored on SQL Server?
There is some concept I am not understanding here.
Thanks for any help
Hi Remus
Thank you for the email reply.
However i am still not 100% clear, dialog security with certificates when there are hundreds of remote services seems a bit complicated.
I have a few more questions.
1. you said "When sending the first message (any direction), we look up the 'best' certificate for each user"
My inititor sevice is owned by dbo, i have created certificates only for named users not dbo, how would this work?
2. I am going to try settin gup this again, but at the central service i will create a seprate user for each remote service, authorize the remote certificate for this user and use this user in the remote service binding for that remote service, is this correct or am i still doing somehting wrong?
3. How does all of this relate to the endpoint security, i ahve the same scenario, 1 pricipal id with all remote certificates authorized for it for the endpoint? I have no idea how i would have to setup different user for each remote servcie as far as the endpoint goes as there is no remote service binding to dictate which user to use ?
I will keep try and experimenting maybe i will get it right sooner or later.
Thank You
Still working on custom security
Since I can't get a name of file (path) from within CheckAccess method it becomes somewhat useless. Is there a way to maybe overload GetPermissions or Policies methods? Or maybe CreateReport method so that I can include some custom code there? Is there an example of something like that?
I wish the catalog tree was transparent to Authentication extension, I don't see a point in acl for a custom extension, all I want is names and I can build on top of that. Something along the lines -
Code Snippet
public bool CheckAccess(...file...)
{
string[] permissions ;
permissions = server.GetPermissions(file);
...
Maybe I am missing something simple and I can tie everything to a security descriptor but I don't see how I can if there is no information such as name, date, modified by name and so on. All we get is principal name which is not very useful since I don't use built-in security names.
Thanks
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
Hi,
I am new to SQL 2005, can someone give me some details instructions about how to do below two tasks:
All my developers are in a window domain user group, I need to grant dbo privileges to that domain group so then can do the their development work. The rule is all objects they create need to be owned by dbo not by there ID. ( I can€™t do it because I got €ś The €śDeafult_Schema clause cannot be used with a windows group€?)
Same as above but this time they only need select permission on tables nothing else.
Many thanks.
PC
<!--[if !supportLists]--><!--[endif]-->
I posted this question before, and got a response about Windows Update. However, I work in a larger corp. and need to know if there are any other answers to this? We use a patch client that rolls out patches and service packs after we have tested them in our environment. What can we do to stay as up to date as possible for security patches?
How are security patches for SQL Server Express made available (e.g., as separate distributions or bundled into other Microsoft patch distribution mechanisms)? Are there specific procedures that I need to put in place to ensure that it gets patched on end user machines?
-Kyle
Greetings. I have developed our own custom security extension, implemented forms based authentication, and can authenticate from report manager, report server and sql studio. So far so good.
However, when it comes to Authorization, i'm unclear in a few areas and would appreciate if someone could help me out with the following questions. It should be noted that in the code I have granted an administrator user full access to all operations and permissions, and then tested against both an administrator user and a normal user.
IAuthorizationExtension.GetPermissions summary says
"Returns the set of permissions granted a specific user for an item in the report server database."
Inparticular, the secDesc parameter is supposed to contain the security descriptor associated with the item.
However, with our extension this parameter is always null, even if I have already granted access for a user, which is confirmed through logging in CreateSecurityDescriptor.
Through the report manager or sql studio I can see that the permissions have been created, so I can't understand why I never see them in the GetPermissions method? This then (seems to) flow through to the various CheckAccess methods, where the users are authenticated, but are not authorized to perform any operations. i.e. in report manager a user has no folders or reports available.
Is RS authorization designed around the concept that the details will always be stored in it's own database?
Ideally, we'd like to have the various roles, users and function authorizations defined in our own security framework. This is working great for the authentication aspect of the extension, but unless there is a mechanism which exposes the details of the particular authorization process (e.g. the name of the folder being viewed or report being run), then I can't see a way we can implement it. Unless i'm missing something fundamental of course!
Using Report Builder as the Administrator user (or any other user), I can see no data models available, even though I have created them via Report Manager, and I get the following exception trying to open up the list of reports:
System.Web.Services.Protocols.SoapException: The permissions granted to user '' are insufficient for performing this operation. ---> Microsoft.ReportingServices.Diagnostics.Utilities.AccessDeniedException: The permissions granted to user '' are insufficient for performing this operation. at Microsoft.ReportingServices.Library.ListChildrenAction.PerformActionNow() at Microsoft.ReportingServices.Library.RSSoapAction.Execute() at Microsoft.ReportingServices.WebServer.ReportingService2005.ListChildren(String Item, Boolean Recursive, CatalogItem[]& CatalogItems)
I have implemented a report server proxy (inherited from ReportService2005) as per the example, to pass through the authorization cookie. Any clues as to what could be wrong?
Finally, I suspect part of my problem may be in assignment of users to System Roles ("System Administrator" and "System User"), I'm not sure if these are meant only for Windows Authentication? I can see no way of assigning these roles to any of my users using Forms Authentication.
Thanks for any help or advice you can give!
Hi:
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.
Joan
Hi there!
I was wondering if anyone can help answer a few questions in regards to MS SQL Server.
1. In stored procedure, am I able to select a bunch of records and run through each record and depending on a certain value do an update or insert?
2. Using DTS, am I able to schedule the stored procedure to run at certain times during the day?
Many thanks!
Richard
Hey all,
I'm wondering what command I could issue to see the amount of memory a named instance is using from within the instance itself. We've enabled address extensions (PAE), and task manager no longer shows the correct amount of mem being used by the process. Under perfmon, I've added the object MSSQL$INSTANCENAME:Memory and I'm looking at the Total Server Memory. I'm seeing 4 gigs, when max memory is capped at 2gigs. This must show the server memory and not the instance memory ? Is there some way to see the instance memory ?
Cheers,
-Kilka
Hi,I copied the DATA directory from a backup ghost image to a new SQL6.5 installation and, after fixing a problem with the directory structure(which caused the databases to be marked as "suspect") I was able to workwith it in a normal way.Opening the company database works without problems. The DATAdirectory seems to be correctly copied. The only problem is that I'm unableto see the scheduled tasks. I'm a using predefined database user (not sa).My questions are:1. Is it normal not to see the scheduled tasks when logged asanother user than the sa or the owner of the tasks ? If it isn't, thensomething went wrong copying the DATA directory (even if SQL didn't markthem as suspect). Any advise or comment about this would be really welcome.2. If it's normal not to see the scheduled tasks, Is there any wayto recover the lost sa password ? The docs containing the password werelost around 4 years ago.Any help would be really welcome,
View 10 Replies View RelatedHi Folks,I am a newbie when it comes to SQL Server 2000 and have a Sybasebackground. How can I reset my sa password in MSSQL like we do it inthe runserver file(-p) in sybase??? Also, can I configure replicationusing the standard edition of MSSQL server????ThanksRegardsdba_sybase2003
View 5 Replies View RelatedDoes anyone know where i can obtain some SQL Server questions to test interview candidates ??
many thanks
Paul
Does anyone know where i can obtain some SQL Server questions to test interview candidates ??
many thanks
Paul
Does anyone know where i can obtain some SQL Server questions to test interview candidates ??
many thanks
Paul
Im consulting a financial company on their Access database. It has a back-end with about 100 tables (some close to 100,00 records) and a front-end with all the Forms, Queries, Modules, and Reports. The last few weeks they have had to compact and repair the front end twice a week where before it was a bi-weekly thing.
Im trying to sell them the service of moving everything I can to SQL Server and link it back to the front-end Access GUI, but Im not sure of everything that goes into this process and how long it will take - of course my client needs the process on paper with an estimate timeline/cost.
My questions are:
1) What does Compact and Repair actually do?
2) Can Access Queries be moved to SQL Server Views and be linked?
3) What can I do about Access Modules and Macros? Can they be moved to SQL Server somehow easily?
4) What other concerns should I have?
Also if any of you fine people have done this sort of thing in the past please share your stories :)
Guys,
Let me first give you a little bit of background...
One of my goals for this year is to migrate all DTS packages to SSIS, those DTS packages are used across the company by different groups/users.
Since I'm still not that familiar with SSIS and like many here, was well used to the enterprise manager view (all DTS packages on 1 single place), I'm unsure on how to structure my SSIS packages in a way that I could break it down by group let's say.
The way that I'm doing right now is: I'm creating a solution for each group and creating the SSIS packages (.dtsx) relative to that group inside each solution, that's pretty easy. Now, the next thing that I'll have to figure out (and hope you guys can help) is how to deploy them structured this way and how to handle these packages to my end users. Basically, how do I handle "Group A" all of their SSIS packages? Will they have to open the package in debug mode on VS every time they need to execute a package?
Thoughts on this?
Hello,
We are currently running a corporate client with Windows 2000 and .Net 1.1. We are running a number of SQL Server 2000 applications and are now thinking of upgrading to SQL Server 2005 as part of a data consolidation exercise. I am concerned on a number of points:
Can I connect to SQL Server 2005 using old ADO connectors? We have about 40 Excel VBA solutions, and we dont want to upgrade to SQL Server 2005 if we will be unable to connect to the data source. We cannot upgrade any new versions of MDAC or upgrade the .NET framework so this is a concern.
Do we need .NET 2.0 or Visual Studio 2005 to connect and work with SQL Server 2005? If so, this will be a problem as we cannot upgrade any client beyond .NET 1.1, and only have VS 2003 as a scripted application we can install for any development.
Has anyone have any experience of the KPI capabilities of SQL 2005? We are bordering on committing to a Business Objects BI platform, and having worked with BO Dashboard Manager and Performance Manager for 4 months (it was horrible), I am not relishing the prospect and would like to propose SQL 2005 as an alternative.
Many, many thanks
Indy
Hi,I have a few questions about sql 2005 as follows:1. Which MS SQL version (edtion) is good as database tosupport a midium size web size?2. I have old *.mdf and .ldf file from ms sql 2000.Does it work if I just copy them (or just *.mdf file) to2005 sql server (any edition).TIA,steve
View 2 Replies View RelatedHi,Is there any website which lists the various SQL Server relatedquestions which might be helpfull for interviews. I am looking forone that contains complex Queries that includes all kinds of operatorslike GROUP BY, HAVING etc...Regards,Kamlesh
View 6 Replies View Relatedwhat are pseudo tables
how queries run by MSDE
how stored procedures run in backend by MSDE
how triggers run by MSDE
where triggers and stored proc stored in MSDE and in which form
where logs are being maintained of transactions/DML statement by default
I asked my tutor those questions and I want to ask you too
Hi
Sir
I hope you doing fine
I
have some questions please;
What is the diffrent between For Attach in Create Database
Command and Sp_Attach_DBWhat is the diffrent between store db file in folder and store
it raw partition e.g. C:What is the useful from TextImage_On in Create Table Command; it
is order Sql Server to stor the photo in diffrent table's fileCan you give us a basic steps to insert *.JPG photo in Sql
ServerIf I have this statement:
Create Table xTable
(
UserId
Int ,
UserName
VarChar ( 3 ) ,
Email
VarChar ( 10 ) Default ( UserId + '_' + UserName )
)
Why This Code not works and how can
I do this without using Triggers.
Can you tell us the different between Media Set and Media Family
and the useful of them. Can we do Backup and Restore Commands on Virtual Tape and How?When I write this code
Create Trigger xTrig on xTable
For Insert
AS
When this trigger will fire Before
Insert, during or after.
What is the useful from EDURE in Create ProcedureWhat is the useful from Recompile in Create Procedure In Sp_AddRole how can I create this role and make the owner
anther person e.g. sa What is the different between User and LogIn in Sql ServerWhat is the different between Role and Group in Sql ServerWhy can I add user to group in Sp_AddUser and I could not add
one in Sp_AddLogin
These lies work good
-- Add
New LogIn to ProgramManagerRole
Exec Sp_AddLogIn 'zProgramManagerUser' , '' ,
'C8_Ahmad_13430_NewSIS' , 'English'
Go
Exec Sp_GrantdbAccess 'zProgramManagerUser'
Go
Exec Sp_AddRoleMember 'ProgramManagerRole' ,
'zProgramManagerUser'
Go
But Why these lines not work good and give me a
msg with an error the Role name or User Name not in the db
-- Add
New LogIn to ProgramManagerRole
Exec Sp_AddLogIn 'zProgramManagerUser' , '' ,
'C8_Ahmad_13430_NewSIS' , 'English'
Go
Exec Sp_AddRoleMember 'ProgramManagerRole' ,
'zProgramManagerUser'
Go
Exec Sp_GrantdbAccess 'zProgramManagerUser'
Go
Thank you
I'm new to using SQL server and have a few setup questions.
I'm setting up a database for the company I work for. Should I setup a different database for each division in our company (e.g. accounting, project management, estimating, etc.), or should I use just one database? It seems like it would be easier to use one database so I only have to use one connection, but is this ideal? I want all the info from the different divisions to be used together, but at this point I'm confused as to how to connect different databases together, if I were to go this way.
Thanks for any input or ideas!
I've installed SQL Server Reporting Services and designed few useful reports on local workstation, now I want to deploy those on the production reporting server.
I have the following questions:
1) We have some demo databases with the similar structure so the the same report would fit all the demos.
Is there any way to change the connecting string for the report programmatically without recreating report each time ? May be there is a way to pass database name to the report (encrypted) ? Is there any way to
2) I'd like to be able to protect those reports from unauthorised users ? Is there any way of doing that ? Is there any way to set up security on the base of folder anf for the whole group of reports ?
Thanks
Mikhail
Our database has grown to the point where our current server is struggeling with the query load. One option is to get a 4 processor machine with 16GB of RAM, but I'm also looking at transactional replication as a solution. Currently we run dual Xeon with 4GB of ram (using the /3GB switch in the OS) We have SQL 2000 Enterprise.
The idea is to setup a secondary server with transactional replication pushed from the main server, so that some SELECT-only queries can be executed on the secondary server - thus taking load of the main one. We should be able to add PKs to the small number of tables that currently don't have them, and we should be able to run all updates / inserts on the main server.I'll setup a push-subscription for the entire DB (maybe excluding some log tables) and then for ceratain stored procedures I'll direct our applications to use the backup instead of the main server.
So: Is this a good idea? Is it easy to backup the server using transactional replication? How much extra overhead will this mean for the main server?
I recently upgraded to sql server 2005 for developing on my local system and cant seem to find the option that automatically sets the drop procedure at the top and the usernames on the bottom of a procedure that I script as new. I used to do it in the old query analyzer so Im sure its in there somewhere. Thanks in advance for any help.RyanOC
View 4 Replies View RelatedHi,
I am trying to learn more about SQL Server, so I can try to make the transition from Access, as I have read, and heard from many people that SQL server is far superior.
I have a few questions that I am unsure of, I have basically no real experience with SQL server.
1) I have been watching the "How Do I" videos, posted on MSDN (which are AWESOME, btw), and for most of their examples, they use the SQL Server 2005 Express which comes with VWD.
I do not have any problem running any of the sample applications on my local machine, but what about for when you want to actually create a web app to publish online? From what I have read, most hosts do not give support for the express edition of SQL Server 2005, so how do you go about changing your app?
For example, if you wanted to use the Login control, which automatically creates a SQL express database for you, can it be changed to implement regular SQL server 2005, or SQL server 2000?
I am just confused as to how to make use of the features available other than on a local machine.
2) Where would be a good place to learn more? Any recommended books?
3) Does anyone know of a good (affordable) host that would offer SQL Server 2005/ASP.NET 2.0 support?
Any advice would be greatly appreciated. I know these are probably very basic questions, but I am very new to SQL server.
Thanks,
Hello - I'm about to rebuild my website on a nice new windows 2k3 server. I was previously using SQL server 7 but I'd like to use the 2005 edition.
Here's my first question - is the Express edition 2005 good enough to use on my live web server? Are there limits on the amount of connections at any one time?
My second question is this - what's the best way to connect to SQL Server 2005 Express?
At the moment I would use something like:
Server=.SQLEXPRESS;Integrated Security=True;Database=myDB
And then I would set up a COMPUTERNAMEASPNET account under SQL Server 2005 Express.
Is that an ok way to connect??
Any ideas?
Thanks.
Hey guys,
I have been developing a SQL based data warehouse for financial reporting purposes. I do expect the database to continue to grow over the next few years.
Right now, I am running SQL Server 2000 on an HP DL380 G3 with dual Xeon 2.8GHz, 1.5GB of ECC DDR RAM, 4x 300GB SCSI 10K RPM HDD's.
I would like to replace the server as it is now two generations old and because when there are five people running queries against the data, it starts taking minutes to return the results. When this reporting database hits production, it will be queried by up to 20 people simultaneously and I don't feel the current server will provide results in reasonable amounts of time.
I would like to know what you people think I should be doing to provide an optimal environment for this data warehouse. Should I stick with SQL Server 2000 or move to 2005? Why would you recommend that?
I am thinking of replacing the server with the following configuration: HP GL380 G5, 2x Xeon E5345 Quad-core CPU's, 4GB PC2-5300 ECC DDR2 RAM, 8x 72GB SAS 15K RPM HDD's. Do you think this is an ideal configuration? Too much? Too little?
I would love to hear what you have to say and suggest. Thank you in advance!
Chris.
DESCRIBE in Oracle = ? in SQL SERVER?
Does anyone have a matrix of Oracle to SQL Server common commands?
I am having trouble accessing my company's hosted SQL Server manuals.
TIA
JEJ