Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Administration
 Problem in SQL Authentication

Author  Topic 

Abid

110 Posts

Posted - 2011-01-26 : 23:51:58
hi, i developed a project in visual studio 2005 (vb.net) and SQL Server 2005 at back end. right.

when i installed the SQL Server so i installed it on Mixed Mode, because i am using SA Login and Password right.

now my project is complete and i have to deliver it to customer but there i got a security issue. the problem is that i developed the project by using SQL Server Authentication Mode not the Windows authentication mode right. but i can login to SQL Server from Windows Authentication mode and can delete my database and tables. Why is it so? i mean that if i deliver project to customer and any non authorized person may log in to SQL Server and can delete tables or the whole database. so how do i keep it secure?

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2011-01-27 : 00:42:10
Disable the not required Window logins in SQL server. Check the following page

http://msdn.microsoft.com/en-us/library/ms189828.aspx
Go to Top of Page

Abid

110 Posts

Posted - 2011-01-28 : 00:44:43
quote:
Originally posted by MIK_2008

Disable the not required Window logins in SQL server. Check the following page

http://msdn.microsoft.com/en-us/library/ms189828.aspx



hi, this is not my answer, you are telling me about howt to change passsord, but i dont need this.
Go to Top of Page

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2011-01-28 : 00:52:24
but i can login to SQL Server from Windows Authentication mode and can delete my database and tables. Why is it so?

While installing a SQL server instance you have only two choices to Select in terms of Logins: 1) Windows Authentication 2) Windows and SQL (Mixed Mode) Authentication.

I think you dont want a person to login through the Windows Account rather should login with SA account, which you have used in the application, in other words trying to protect the Schema of your databases from Clients :)

Therefore, as a hint i have told you that you can DISABLE a Login, so that no one can access even the SQL instance. The above link have all necessary information.

Cheers!

either way a windows account will be added as a login to the SQL sever :)

Now coming to
Go to Top of Page

Abid

110 Posts

Posted - 2011-02-02 : 00:11:13
quote:
Originally posted by MIK_2008

but i can login to SQL Server from Windows Authentication mode and can delete my database and tables. Why is it so?

While installing a SQL server instance you have only two choices to Select in terms of Logins: 1) Windows Authentication 2) Windows and SQL (Mixed Mode) Authentication.

I think you dont want a person to login through the Windows Account rather should login with SA account, which you have used in the application, in other words trying to protect the Schema of your databases from Clients :)

Therefore, as a hint i have told you that you can DISABLE a Login, so that no one can access even the SQL instance. The above link have all necessary information.

Cheers!

either way a windows account will be added as a login to the SQL sever :)

Now coming to





Hello, MIK, would you please tell me that can i do this from sql server without this querty which is describe in this MSDN link.? i mean i am not understanding this so how do i apply this, i dont know. so can i do this thing some other way?
Go to Top of Page

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2011-02-02 : 00:28:11
Yaar follow this path: SQL server Instance's Object Explorer - Security - Login - Properties - Status (tick deny and disabled and then Save/OK)

Cheers!
MIK
Go to Top of Page

Abid

110 Posts

Posted - 2011-02-04 : 07:24:19
quote:
Originally posted by MIK_2008

Yaar follow this path: SQL server Instance's Object Explorer - Security - Login - Properties - Status (tick deny and disabled and then Save/OK)

Cheers!
MIK



hi, boss, its not wokring, when i press Deny and OK, so when i connect next time so it allows me to connect and its status is again back to Grant
Go to Top of Page

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2011-02-04 : 08:18:52
Remove the BUILTIN\Administrators login.

Be careful though, make saure you still have a sysadmin account that you can use.
Go to Top of Page

Abid

110 Posts

Posted - 2011-02-09 : 01:23:15
quote:
Originally posted by Abid

quote:
Originally posted by MIK_2008

Yaar follow this path: SQL server Instance's Object Explorer - Security - Login - Properties - Status (tick deny and disabled and then Save/OK)

Cheers!
MIK



hi, boss, its not wokring, when i press Deny and OK, so when i connect next time so it allows me to connect and its status is again back to Grant




HI MIK, would you like to suggest something else.
Go to Top of Page

Abid

110 Posts

Posted - 2011-02-09 : 01:24:37
quote:
Originally posted by russell

Remove the BUILTIN\Administrators login.

Be careful though, make saure you still have a sysadmin account that you can use.



hello russel, please tell me how do i do this? and will it solve my problem?
Go to Top of Page

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2011-02-09 : 01:34:09
I am still sticked to what i said and also agree with Russel as an alternate
Go to Top of Page

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2011-02-09 : 01:36:15
[code]IF EXISTS (SELECT * FROM sys.server_principals WHERE name = N'BUILTIN\Administrators')
DROP LOGIN [BUILTIN\Administrators]
GO[/code]

If you're installing a database at a client site, it won't be terribly difficult for them to sniff around it if they are determined. But removing [BUILTIN\Administrators] will at least make 'em try harder to get in.

What is so sensitive that you don't want them to see? If it truly is that sensitive, then you can use encryption.
Go to Top of Page

Abid

110 Posts

Posted - 2011-02-09 : 09:21:03
quote:
Originally posted by russell

IF  EXISTS (SELECT * FROM sys.server_principals WHERE name = N'BUILTIN\Administrators')
DROP LOGIN [BUILTIN\Administrators]
GO


If you're installing a database at a client site, it won't be terribly difficult for them to sniff around it if they are determined. But removing [BUILTIN\Administrators] will at least make 'em try harder to get in.

What is so sensitive that you don't want them to see? If it truly is that sensitive, then you can use encryption.




when i installed the SQL Server so i installed it on Mixed Mode, because i am using SA Login and Password right.

now my project is complete and i have to deliver it to customer but there i got a security issue. the problem is that i developed the project by using SQL Server Authentication Mode not the Windows authentication mode right. but i can login to SQL Server from Windows Authentication mode and can delete my database and tables. Why is it so? i mean that if i deliver project to customer and any non authorized person may log in to SQL Server and can delete tables or the whole database. so how do i keep it secure?
Go to Top of Page
   

- Advertisement -