Hi all,
I'm having a little problem with my sqlserver 2005.
I'm trying to crate a user in Microsoft SQL Server Management Studio throug a query.
Like this:
-
USE [master]
GO
CREATE LOGIN [MYUSER] WITH PASSWORD=N'MYPASSWORD', DEFAULT_DATABASE=[MYDB],
DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
ALTER LOGIN [MYUSER] ENABLE
GO
USE [msdb]
GO
CREATE USER [MYUSER] FOR LOGIN [MYUSER];
GO
GRANT EXECUTE ON [sp_start_job] TO [MYUSER]
GO
EXEC sp_addrolemember 'db_datareader', 'MYUSER'
GO
EXEC sp_addrolemember 'db_datawriter', 'MYUSER'
GO
EXEC sp_addrolemember 'SQLAgentOperatorRole', 'MYUSER'
GO
EXEC sp_addrolemember 'SQLAgentUserRole', 'MYUSER'
GO
EXEC sp_addrolemember 'SQLAgentReaderRole', 'MYUSER'
GO
Use [MYDB]
GO
CREATE USER [MYUSER] FOR LOGIN [MYUSER]
GO
EXEC sp_addrolemember 'db_datareader', 'MYUSER'
GO
EXEC sp_addrolemember 'db_datawriter', 'MYUSER'
GO
And drops the user by executing following:
Use [MYDB]
GO
EXEC sp_dropuser 'MYUSER'
GO
USE [msdb]
GO
REVOKE EXECUTE ON [sp_start_job] TO [MYUSER]
GO
EXEC sp_droprolemember 'SQLAgentOperatorRole', 'MYUSER'
GO
EXEC sp_droprolemember 'SQLAgentUserRole', 'MYUSER'
GO
EXEC sp_droprolemember 'SQLAgentReaderRole', 'MYUSER'
GO
EXEC sp_dropuser 'MYUSER'
GO
USE [master]
IF EXISTS (SELECT * FROM sys.server_principals WHERE name = N'MYUSER')
DROP LOGIN [MYUSER]
-Then if I from a query, first creates the user, then drops the user, I can't create him again? I get's the following error when trying:
Msg 10054, Level 20, State 0, Line 0
A transport-level error has occurred when sending the request to the server. (provider: TCP Provider, error: 0 - An existing connection was forcibly closed by the remote host.)
I'f i then runs the createscript again, the user is created correctly.
Is there a way to avoid this foced lockout? Or how do i "refreash" my connection, without dataloss?
Best Regards
Henrik
Have you tried executing your scripts from sqlcmd instead of Management Studio? Are you seeing the same behavior?
Thanks
Laurentiu
Hi Cristofor,
Thanks for you reply. I'v just tried that and get following:
HResult 0x2, Level 16, State 1
Named Pipes Provider: Could not open a connection to SQL Server [2].
Sqlcmd: Error: Microsoft SQL Native Client : An error has occurred while establi
shing a connection to the server. When connecting to SQL Server 2005, this failu
re may be caused by the fact that under the default settings SQL Server does not
allow remote connections..
Sqlcmd: Error: Microsoft SQL Native Client : Login timeout expired.
But under "Server Properties" - "Connections"
The "Allow Remote connections to this server" is checked, with a query timeout on 600
|||In some SQL Server SKUs the remote connection is disabled by default. Use SQL Server Configuration Manager to enable TCP/IP (or named pipes) on your SQL Server instance (requires to restart SQL Server service). Also make sure the Windows firewall is configured to allow SQL Server to accept connections.
-Raul Garcia
SDE/T
SQL Server Engine
|||Hi Raul,
I have just enabled this it was disabled, but still im getting the same error.
But by executeing the script localy from the database server, it works fine. And can do it as many times as i please, whithout errors.
like this:
sqlcmd -sMYDBSERVER -E -i "C:\drop.sql"
And
sqlcmd -sMYDBSERVER -E -i "C:\Create.sql"
But it still drops the connection to the user elsewhere, on the first time it's used after creation.
Eg. First i drop the user, then i create it again with the scripts. Then i open a browser and goes to my website, wich uses this user to connect to the database to retrive and update information. Then the first time i try to login i get the error below. Then i push "F5" to refreash the page and all works fine again?
My only guess is that your web application is somehow caching/reusing the old connection (with the old login), and when you refresh the page it reestablishes the connection. Remember that when you drop and create a login it is a whole new principal, not the same one (even if the name and password is the same), and any established connection will be invalidated and the connection will be forcedly terminated by the server the next time any application tries to use it.
I would recommend using SQL profiler to trace back new/closed connections as well as existing sessions to try to detect if this is indeed the case.
-Raul Garcia
SDE/T
SQL Server Engine
|||Hi,
Thats also what im comming to. I haven't tryed the SQL profiler yet, but will do this later.
But i have found out that it's not enough only to stop and start the web site again on IIS. I have to restart the hole "IIS" to make the "refreash". So im thinking that the session is not terminated correctly from the website, when closing the browser/session?.
|||Besides your investigation on why (and if) the web application is reusing the connection instead of reconnecting, I have one question for you: are you dropping and recreating this user in order to create a whole new principal (i.e. a new SID, with no existing objects, etc.), or just as a mechanism to prevent this principal from establishing a new connection?
If you are using these scripts to prevent new connections, you may also want to consider disabling the login instead. By disabling the login, you prevent new connections without affecting existing ones, and without the need to cleanup any objects or references (i.e. users) this principal may have in the system. You can find the syntax for this operation in ALTER LOGIN (http://msdn2.microsoft.com/en-us/library/ms189828.aspx).
-Raul Garcia
SDE/T
SQL Server Engine
|||
Actually im useing thise scripts as a part of my deploymentkit. This is to ensure that all securitysettings to any databases, stored procedures etc are droped. So i afterwards can replace them with either the same or new ones, without leaving anything behind.
But i took the change and implemented it into my deployment. The first time i got the same error about the "transport-level error". But that was because i've missed another scipt later in the installation, it was some inserts-stament i tried to execute from a vb SqlConnection. Then i moved those two things around, so it was the last code and then it worked fine, i can now open my webpage without the "transport-level error" :) yeah .. even after installing, uninstalling, installing ....
So i have to go back to the websession-closing, in my case, because under the "Activity Monitor" in sql, i can see a connection long after closing the browser. So this is now another problem for me, to find a way to kill this one.
But im stil interested in fixing the other problem, because it's annoying that things has to come in the correct order.. And the differens between executing it from a sql-query (in sql 2005), from command promt and from my msi-package. -Wich by the way executes it by calling a shell in vb wich runs a .cmd-file containing the two sql-scripts.
sqlcmd -sMYDBSERVER -E -i "C:\drop.sql" -o "C:\DropDBUser.log"
sqlcmd -sMYDBSERVER -E -i "C:\Create.sql" -o "C:\CreateDBUser.log"
But for the moment im happy :)
|||
I’m glad to hear that. Feel free to let us know if you have further security questions in SQL Server, we are always looking forward to help our customers whenever we have a chance.
-Raul Garcia
SDE/T
SQL Server Engine
