Wednesday, September 19, 2012

SET SPN on the SQL service account

It´s not so easy to find a clear roule how this shall be done. But this is what I use and it works.
You have to setup three SPN-s, Full FQDN against the port (be sure to use static), FQDN against the instancename and a third one against the Netbios name.

SETSPN -s MSSQLSvc/SERVERNAME.DOMIANNAME.COM:port DOMAIN\SQLSERVICEACCOUNT
SETSPN -s MSSQLSvc/SERVERNAME.DOMIANNAME.COM:Instancename DOMAIN\SQLSERVICEACCOUNT
SETSPN -s MSSQLSvc/SERVERNAME DOMAIN\SQLSERVICEACCOUNT

For the analysis service those two are needed

SETSPN -s MSOLAPSvc.3/SERVERNAME.DOMIANNAME.COM:Instancename DOMAIN\SQLSERVICEACCOUNT
SETSPN -s MSOLAPSvc.3/SERVERNAME:Instancename DOMAIN\SQLSERVICEACCOUNT

In some situation also the browser needs to be fixed.

SETSPN -s MSOLAPDisco.3/SERVERNAME.DOMIANNAME.COM SERVERNAME
SETSPN -s MSOLAPDisco.3/SERVERNAME SERVERNAME

Beyond of the Kerberos SPN setting you also need to set the serviceaccount trusted for delegation in active directory. Client will use Kerberos anyway but in some situation
where you have more then two machines involved in the authentication chain this is necessary.



Monday, September 3, 2012

Query Excel and text files from SQL

Here is some different ways to query an excel sheet or textfile.

Excel..
SELECT * FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0',
'Data Source=G:\test.xls;Extended Properties=Excel 8.0')...[sheetname$]

SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=G:\test.xls', [sheetname$])

SELECT * into import FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=G:\test.xls', 'SELECT * FROM [sheetname$]')

Texfile..
select * from OpenRowset('MSDASQL',
'Driver={Microsoft Text Driver (*.txt; *.csv)}; DefaultDir=C:\Temp;Extended properties=''ColNameHeader=True;Format=CSVDelimited;''','select * from test.csv')

SELECT a.* FROM OPENROWSET( BULK 'c:\test\test.txt',
FORMATFILE = 'c:\test\formatfil.fmt') AS a;

Most of the time I have test to query textfiles from OPENROWSET it is difficult to delimiter the columns in the text file. I have never seen the Format=CSVDelimited settings having any affect. But the grate site connectionstring.com pointed out that you must set this in the registry on the server. And this works well for me. See example below. I wonder why the setting is available in the Extended Properties then?? At least I have never got it to work. But that says nothing :-)


The delimiter can be specified in the registry at the following location:

HKEY_LOCAL_MACHINE \ SOFTWARE \ Microsoft \ Jet \ 4.0 \ Engines \ Text 
"Format" = "TabDelimited" or "Format" = "Delimited(;)"