Siusic Dot Com

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.

IE8 came out not long ago and I just installed it and try it out knowing that I would care much about its new features. But I was disappointed the first time I used it after installation. I went to Google Map it didn’t work and I went to Yahoo Map it didn’t work either. I mean the entire map was not able to display on IE8. When I went to Yahoo map I got a java script error saying, “YMapconfig is undefined” or something like that. I was wondering what went wrong with Microsoft. So what I have Firebox I don’t have to use Microsoft.

I didn’t find the problem until I went to Youtube. Youtube said my IE8 security setting prevented the video from being played. That is true. Seems to me the the default security level IE8 choose for internet zone is medium high level. After I set the security level to medium level everything works just fine. So how to do that? Go to Tools menu, go to internet options, go to security tab, select internet zone, drag the level bar to medium an apply. That is it!

The other day when I installed SQL server 2000 on a 64 bit machine a dialog box pop up and said I couldn’t install SQL server 2000 using the current installation program and and I had to use another executable under a different folder to do installation. Later on a person asked me whether the SQL server installed on the 64 bit machine was a 32 bit or 64 bit version. I said it was of course a 64 bit version because the 32 bit version doesn’t work on 64 bit machine. It was embarrassed to find out I was wrong. In fact even though the 32 bit installation program asked me to use another installation program that program was still a 32 bit installation. The version information of SQL that are running can be returned using the following query

SELECT @@VERSION
SELECT SERVERPROPERTY(’ProductLevel’)

If the version returned is x86 that means it is a 32 bit installation and if it is x64 then it means it is a 64 bit installation. As simple as that! 32 bit version works on 64 bit machine but not vice versa

Well it took me a while to figure out the easiest way to connect to a SQL server listening on a non standard port. The standard sql server port is 1433. When you connect to SQL server using enterprise manager or Query Analyzer this is the port the tools automatically connect to on the server. However when you change SQL Server so that it runs on a non standard port for security reason you will have to do additional trick in order to be able to connect to sql server.

One way to do that is to insert an entry using SQL Server client network utility and specify the port corresponding to the SQL Server you want to connect to. The easiest however way is to embed the port number in the SQL Server name. But how? An almost universal format to specify port with host name is in this way, “ServerName:ServerPort”. For instance 127.0.0.1:1433. However SQL Server doesn’t take this format. It has its own format, “ServerName, ServerPort”. For example 127.0.0.1,1433. So next time when you connect to a SQL Server listening on a non standard port you can put the server name and port number together in above format and place it into where you usual enter the SQL Srver name. It works perfect for both Enterprise Manager and Query Analyzer. And I recalled that was one of the interview question I was asked when I was looking for a job last year.