About SQL Server 2005 Management Studio

i would like to ask if there is a way to FILTER THE IPADDRESSES of users who wants to login the SQL Server Management Studio. right now, we are using Enterprise Edition of SQL Server 2005.

i just want to limit the users who can log in at Management Studio, the users know the password of SA, and i can't change the password coz we have deployed the programs with odbc connection using SA. so i thought of filtering the ipaddress of computers i want to grant access to SQL Server Management Studio, if its possible.

need a help.

thanks

I'm not sure you are able to do this unless you can write some sort of database/server scoped trigger that checks which application/IP the request is coming from (does anyone know if thats possible?).

However, remember that SSMS is just a GUI intended to make administration easier and restricted access to this tool doesn't really plug the serious security risk you have in using the sa password. Malicious users would still be able to use other methods of accessing the database eg osql.

Obviously, you should seriously consider stopping your application using the sa account for database access. At the very least, its good practice to create another user with sysadmin priveleges (eg saUser) and use this instead though operating a policy of least privelege is most best practice.

HTH|||

thank you richbrownesq.

About SQL Login

Hi all
Does someone know a strored procedure or any t-sql that lets you know wheter a login is windows or sql authentication ?You can query the following tables:
sysusers for users and syslogins for logins.
You will find columns like issqluser or issqluser.
This columns will inform you if the user (login) is windows or sql user (login).

About login into sql server via asp.net

I want to design a form with C# that will able me to log in to my sql server with my sql login name and password.This should be the entry point, which is like the "SqlWebAdmin", Where i login first then have a view of databases that i have permission. The problem first is how to login into the sql server via ASP .net without knowing prior which database is mine?Any help plz?Most apps put their connect information in web.config, and read it out at run time.

About Login Account

Hi everyone,
I have a simple question for you which is too important for me.
My question is about logins in my own(local) server.
I know that my local server(my computer's server) has to default login account which are called sa and BUILTIN/ADMINISTRATOR. While I am using my own server, I do not know which default login account is used by my Server since I use windows authentication while connecting to the Server.
Another question is that while adding new login account for my own Server, I noticed that there is a choice which is comprised of Grant and Deny for the Security. What is the meaning and usage of this ?

Thanks and Regards

I suggest reading some of the security papers of Microsoft or other sites covering SQL Server Authentication, like

http://www.developer.com/tech/article.php/721441

For the beginning , sa is the sysadminstrator account (and which is a SQL Server login), Builtin/Adminstrators is a WIndows groups which is also in the role of the sysadmins by default.

You can either Grant Access to a SQL Server database or Deny access. Grant permissions are always additive, deny is restricitve, which means that if a user is in a special Windows group which is allowed to access the server but is in another group which is denied access to the server, access will be denied to him.

Jens SUessmeyer.

http://www.sqlserver2005.de

|||

Thanks for your good tutorial reference but I could not understand the following paragraph.
Would you mind explaining it to me clearly ?

With Windows Authentication we recommending placing users into NT groups and adding the NT group login to SQL Server. This powerful feature allows you to group your users together at the NT Domain level and apply permissions at the SQL Server level. For example a group named sales could exist in the NT Domain. A new login based on the NT sales group could be added to SQL Server. When a new NT account is created the NT administrator can add the new account to the NT group named sales. The SQL Server DBA can apply the appropriate role(s) and permissions to the sales group. The new NT login automatically contains the necessary permissions to log in to SQL Server because the NT sales group was previously granted a login. This strategy makes it easy for new users to be added the system.

|||

Sure,

assume the following situation. You have a production department with 5000 employees. One day you create a table storing information about timesheet. you want every employee to enter his worked time into that table. You could either grant all 5000 employees individually rights on the table to insert data or you can use the possible existing Windows group which already includes all the 500 employees and which is maintained through the active directory. So if any user will leave the company / production you don′t have to keep track as a database adminstrator of the security, because as the employee is deleted from the group he will have no longer access to the table. So you only have to do the work at a single point, the Active diretory.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||

Indeed, thanks very much for your explanatory reply but I wonder that how I can create a Windows group authentication.

|||Hi,

first some more to read :-) http://www.microsoft.com/technet/prodtechnol/sql/2000/reskit/part3/c1061.mspx?mfr=true

If you have a domain simply create a Windows group, put user in and create a WIndows login in SQL Server naming the group. Although the creation dialog is named Windows login you can both create a Windows login from a user or a group.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de
|||

Thanks for your explanatory reply, but I have still some doubts about Windows Authentication.
Is it really possible to use Windows Authentication in order to connect an another SQL Server in my computer ? I think that we can only use Windows Authentication mode for our own local server, is it true ?

|||

Jens Suessmeyer wrote:

I suggest reading some of the security papers of Microsoft or other sites covering SQL Server Authentication, like

http://www.developer.com/tech/article.php/721441

For the beginning , sa is the sysadminstrator account (and which is a SQL Server login), Builtin/Adminstrators is a WIndows groups which is also in the role of the sysadmins by default.

You can either Grant Access to a SQL Server database or Deny access. Grant permissions are always additive, deny is restricitve, which means that if a user is in a special Windows group which is allowed to access the server but is in another group which is denied access to the server, access will be denied to him.

Jens SUessmeyer.

http://www.sqlserver2005.de

Another question is that I connect my own SQL Server with Windows Authentication mode.
When I tried to connect with SQL server Authentication mode, I could not accomplish it.
So for uor own local server, is not it allowed to connect SQL Server with SQL server Authentication mode.
Besides, As I said before, I connect my own SQL Server with Windows Authentication mode, so does this mean that I use default Builtin/Adminstrators login in order to connect ?
|||

Yes you are appearantly using Windows authentication. The mentioned group is configured by default to have access to SQL Server. If you can′t access your Server using SQL Server authentication you might not have switch the authentication mode to mixed. For that navigate in the managment Studio to the server properties, in the security tabs and switch to "Mixed Mode", you should be able to connect with any SQL Server login then (by default there is a user with the name sa (system administrator). (make sure you restart your SQL Server in order to apply the changes)

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||

Again thanks vey much for your explanatory reply and would you mind also replying my second question since they make me very confused ?

Thanks and Best wishes

|||

Mhmm, don′t really know which question you mean but if you mean the BUILTIN\Administrators question: YOu can anser this on your own, are you in the administator group ? If yes, you are currently using that. You can have several group memberships all the permissions set in these groups are additive as long as you will get a permission set which denys anything to you.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||

Hmm, thanks but this is not really the answer of my question.
I asked that in my local server, while I am connecting to this (my local server(my computer's server) ) , which role do I have , sa(Sytem Admistrator) or BUILTIN/ ADMISTRATOR.
And besides, I am connecting to my own local SQL server with windows Authentication so does it means that I am BUILTIN/ ADMISTRATOR ?

Thanks

|||

Jens Suessmeyer wrote:

Sure,

assume the following situation. You have a production department with 5000 employees. One day you create a table storing information about timesheet. you want every employee to enter his worked time into that table. You could either grant all 5000 employees individually rights on the table to insert data or you can use the possible existing Windows group which already includes all the 500 employees and which is maintained through the active directory. So if any user will leave the company / production you don′t have to keep track as a database adminstrator of the security, because as the employee is deleted from the group he will have no longer access to the table. So you only have to do the work at a single point, the Active diretory.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

My another question is that as I understood form your reply, firslt we should create a domain name for this Window Group. Then, we add all our corresponding users into this domain while using Windows Authentication for our group. So I ask that how can I create a group( domain name) ?

Again thanks.
Sorry for asking too much question but they really make me confused too much.
Regards|||YOu can either login using SQL Server authentication, then you have to specify a SQL Server account and apssword during the login OR you can use the Integrated security (you don′t need to provide the credentials then because it is integrated and uses your Windows context)

I don′t know in which groups you belong on your local server, but if you are using the default settings, didn′t grant any other group access to the SQL Server (and though this is your own server) I assume that you use the group memebership of BUILTIN \Administrator to authenticate against the server.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de
|||

Jens Suessmeyer wrote:

YOu can either login using SQL Server authentication, then you have to specify a SQL Server account and apssword during the login OR you can use the Integrated security (you don′t need to provide the credentials then because it is integrated and uses your Windows context)

I don′t know in which groups you belong on your local server, but if you are using the default settings, didn′t grant any other group access to the SQL Server (and though this is your own server) I assume that you use the group memebership of BUILTIN \Administrator to authenticate against the server.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

Again and again thank very much for your all extreme explanatory replies.

Now I think I kept the idea. As I understood, By default, I connect my own local server with Windows Authentication as a BUILTIN \Administrator.This group is not regarded as a Group in my opinion. Because, we can not add new user into this group. There is only one person who is the owner of the local SQL server(me) in this group. So what is the real advantage of this group ?
Meanwhile, how can I learn my sa(System administrator)'s password in order to connect my own local SQL Server as a sa(System administrator) ?

Thanks

about connections and Login

Hey guys,

I have created dtsx packages and i have one master package which would run couple of child packages.

1. I have all on my local machine.I want others who r working with me access these.So what do i need to do?for them to access...

2.what would be the best way in executing these packages.

the way it is set now is i had 3 connections set for each package.

a.Source connection

b.Destination Connection.

c.One more connection(which is used with in the process of execution)

Can i set connections in common for all the packages in one location.

3.i also want to set some logging settings to my master package too.and i want this package to run everyday automatically at a particular time how can this be done.

How can i do that.

please let me know.

sureshv wrote:

1. I have all on my local machine.I want others who r working with me access these.So what do i need to do?for them to access...

What you mean, to edit them? just use VSS or place them in a shared location. To execute them, place them in a shared location and then set up a Job (SQl Agent) to run them.

sureshv wrote:

2.what would be the best way in executing these packages.

Via SQL Agent job would be my preferred method

sureshv wrote:

Can i set connections in common for all the packages in one location.

No, but you can use package configuration instead.

sureshv wrote:

3.i also want to set some logging settings to my master package too.and i want this package to run everyday automatically at a particular time how can this be done.

To schedule them, again, use a SQL Agent Job. For logging just enable package logging.