November 27, 2012 at 1:37 pm
Hi guys, I'm very new to sql and MSSM 2008.
I have management studio 2008.
I made a program to read my database.
I enabled server authentication and made a strong PW for the 'sa' account. (why i enabled SA, beacuse I thought to remotely login from other networks off site, I needed this?, correct me if im wrong!)
I opened port on my router to allow myself to remotely login and see my database entries from any computer using a little program/tool I made.
When I logged in, it let me log in and see my database, even with the WRONG username and password!
Is there some sort of guest account enabled? I feel like this is a security risk! I don't want any computer or anyone being able to see what is in the database unless a password is matching a database user. Basically at this point, if they know my IP address, they can login and see what is in my tables. I haven't tried to update or insert records from the remote computer yet.
But the goal is to have remote access from other networks off site being able to ONLY see what is in the database by using a username/pw, WITHOUT being able to update/insert records or edit or delete anything.
Is there a way to manage all this inside MSSM? because I don't know any other way since im totally a newb at this point... Trying to learn all on my own.
Thank you so much
November 27, 2012 at 1:49 pm
I enabled server authentication and made a strong PW for the 'sa' account. (why i enabled SA, beacuse I thought to remotely login from other networks off site, I needed this?, correct me if im wrong!)
You should create a login to use for yourself and not use SA. SA would give you too much permission for what you require.
When I logged in, it let me log in and see my database, even with the WRONG username and password!
When you are logged in try running the following to see if you are logged in with the user you think you are.select suser_sname()
There is a guest database user but not a login for the server
this is an example for creating a log and user. and giving that user read permissions
CREATE LOGIN [<username>] WITH PASSWORD=N'<password>', DEFAULT_DATABASE=[<dbname>];
USE [<dbname>];
CREATE USER [<username>] FOR LOGIN [<username>];
EXEC sp_addrolemember 'db_datareader','[<username>]';
November 27, 2012 at 2:43 pm
Bob, thanks for the reply 1st of all.
So I disabled SA account and made my own account which has only 'public' as a server role. and I set the user mapped to the databases I want them to go to.
I logged in using a SQL client on my cell phone actually. I was on a different network and was not using windows authentication. I tried to access my databse and it allowed me. I tried a different database(which was setup by someone else I think, cuz someone else set this whole computer sql up I should mention.) and it said not enabled for GUEST ACCOUNT.
So I assume it is allowing guest account somehow, but in MSSM when I look up users, I do not see any account labeled as guest!
November 27, 2012 at 2:58 pm
well lets give this a try.
run the following in SSMS when you are logged in to your remote mobile client
SELECT session_id,login_name,host_name
FROM sys.dm_exec_sessions
where session_id > 50
it will list all users currently logged into your SQL Server instance and the host they are connected from. Not sure but it will probably be null for your remote client. you can use this to identify what user is being used when you login.
A guest account should exist in all databases but it should be disabled by default. Since you are unable to see the guest account i have to ask what are your permissions on the server and database?
as for SA, i wasn't suggesting to disable the account just not to use it in the context that you were.
November 28, 2012 at 11:30 am
So I found out it IS the guest account. But in MSSM there is no guest account listed.
I am indeed administrator, and on the actual physical computer that has mssm installed. Everything I do, from adding an admin to deleting a user I am allowed to do in MSSM without restriction. So I know I have full admin rights. I just can't see the guest account option.
Thoughts?
November 30, 2012 at 12:58 am
bump, anyone? should be an ez thing to disable right?
November 30, 2012 at 3:59 am
There is no guest login. If you don't have either a username and password or a windows login granted access to the DB, you cannot log in to SQL.
There are guest users in databases, that's for when someone has a valid login but no user mapped to that DB. Typically the guest user has no rights.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply