Siusic Dot Com

I have not used the developer edition of SQL server on my laptop machine for a long while. When I try to turned on SQL server today I found it couldn’t start. When I check the event log there was an error message related to SQL server service like this.

“The file “C:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\DATA\mastlog.ldf” is compressed but does not reside in a read-only database or file group. The file must be decompressed.”

I remember the free space on my laptop machine was running low and windows ask me to compress the files that were not use frequently. So that was the reason cause this error. I looked at the property of the MDF and LDF files. At the advance tab the check box “compress content to save space” was checked. So I unchecked this box and save. After that SQL server can start without problem

SQLCmd is a very handy tool used to execute SQL script automatically and output results to a file. Here is an example:

sqlcmd -S “ServerName” -U “UID” -P “PWD” -d “DBName” -i “c:\input.txt” -o “c:\output.txt” -s “,” -h -1

The parameter names in command line are case sensitive. So -D is different from -d. As shown in the example most of the parameters are self-explanatory. You can use SQLCmd /? to display all applicable parameters. Parameter -h -1 instructs the utility not to output result set header. Parameter -s “,” instructs the utility to use comma as column separator. The default is to a space.

I was trying out IIS7 the other day. When I was trying to set up a new site I got the following error message.

Error Summary
HTTP Error 500.24 - Internal Server Error
An ASP.NET setting has been detected that does not apply in Integrated managed pipeline mode.
Detailed Error Information
Module ConfigurationValidationModule
Notification BeginRequest
Handler StaticFile
Error Code 0×80070032
Requested URL http://www.gadgetroute.com:80/default.htm
Physical Path C:\testweb\default.htm
Logon Method Not yet determined
Logon User Not yet determined
Most likely causes:

* system.web/identity@impersonate is set to true.

Things you can try:

* If the application supports it, disable client impersonation.
* If you are certain that it is OK to ignore this error, it can be disabled by setting system.webServer/validation@validateIntegratedModeConfiguration to false.
* Move this application to an application pool using Classic .NET mode - for example, %SystemRoot%\system32\inetsrv\appcmd set app “Default Web Site/” /applicationPool:”Classic .NET AppPool”

(You can set “Classic .NET AppPool” to the name of another application pool running in Classic managed pipeline mode)
Links and More Information If you are not sure or unable to use the first two options, then it is preferred that you move this application to Classic mode.

The solution it suggest is not straight forward enough. Simply put the error can be corrected by adding the following bold line in the web.config file

<configuration>
<system .webServer>
<validation validateIntegratedModeConfiguration=”false” />
</system>
</configuration>

The other day I was told to install SQL Server 2000 on to a server that was already running SQL Server 2005. The 2005 version SQL server was running as the default instance on the server. So obviously I had to install SQL Server 2000 as a named instance and I installed it as ServerName\SQL2000. The installation went fine and I was able to launch query analyzer and connected to the SQL2000 instance. The problem was no one else was able to connect to this named instance on the server remotely. It took me sometime to figure out what happened.

I verified that the SQL2000 instance was indeed running. The fact that I could connect to this instance by launching Query Analyzer on the server already proofed that. I tried creating and deleting database. No problem I saw the database files created and deleted on the server. But went I tried to connect to this instance from a remote computer using Query Analyzer it failed. I originally suspected there was firewall or something. So I tried pinging the server. I got response and it showed the same IP address as when I ping it on the Server itself. I open up SQL server network utility on the server and found the port number that the SQL2000 instance was listening on and then I tried to telnet to this port on the remote computer. I was able to connect to this port on the remote computer. So it proofed there was no firewall blocking the connection to the SQL2000 instance.

I could not connect to the instance using the name “ServerName\SQL2000″ but I have verified the port was listening so I tried to connect to “Servername,portnumber” and it worked. So what went wrong? If I use the instance name it didn’t work but went I use the port number it worked. It turned out that the SQL Browser Service on the server was not running. What this service does is to tell remote client what SQL server instance is running on the server. It make sense that if this service is not running then no one know what SQL2000 instance is and no one can connect to it. The problem was solved after I turned on the service.

The best way to grant user who is not a member of sysadmin the right to execute xp_cmdshell system store proc is to have xp_cmdshell wrapped into a user store procedure so that users can do just the things you want them to do with xp_cmdshell without granting them explicitly the right to execute xp_cmdshell freely.

In order to do that the user store proc that calls xp_cmdshell has be to created within the master database and it has to be created under the same owner (dbo) of xp_cmdshell. Only in that case permission to execute xp_cmdshell is not checked when user executes the user store proc that wraps an xp_cmdshell call.

For example I want user Tom to be able to enlist the local C: drive. I want him to be able to execute the following statement exec xp_cmdshell ‘dir c:\*.*’ but I don’t want him to execute xp_cmdshell freely. Here is what I can do. I login to SQL server as sa (or as any user that is dbo of master). Create the following store procedure in the master database:

Create Proc usp_DirC as exec master.dbo.xp_cmdshell ‘dir c:\*.*’

After that I add Tom as a user in the master database and grant him the execution right on usp_DirC. Now Tom can execute usp_DirC and see what files and folders are on the C drive but if Tom trys to run xp_cmdshell directly he will get permission deny. Why isn’t the permssion to execute xp_cmdshell checked when it is executed in usp_DirC? That is because I own both usp_DirC and xp_cmdshell and when I grant Tom the right to execute usp_DirC I also grant him to manipulate whatever objects I own in any way that usp_DirC is defined. But if xp_cmdshell were owned by someone else then SQL Server will definitely check Tom’s permission. In that case usp_DirC will look like this

Create Proc usp_DirC as exec master.someone.xp_cmdshell ‘dir c:\*.*’

and Tom will be denied to run someone.xp_cmdshell.

The question comes why can usp_DirC created in a user database instead of the system database master? As I mentioned earlier both usp_DirC and xp_cmdshell have to be under the same owner in order for this to work. The dbo on one database is not the same to the dbo of another database. So if usp_DirC is created in user database then usp_DirC and xp_cmdshell will be under different owner and Tom’s permission to execute xp_cmdshell will be checked.

One exception is when cross database ownership chaining is enabled. In that case dbo across different databases are the same. So master.dbo.xp_cmdshell and pubs.dbo.usp_DirC are under the same owner and in this case and Tom’s permission to execute master.dbo.xp_cmdshell within pubs.dbo.usp_DirC will not be checked. That is why enabling cross database ownership chaining is not a good thing to do in terms of security.